Im creating a table report with Planned Completion Date and Actual Trend Date as Measures. The dates showing currently are in the month day year format (May 10 2024). Our company communicates in work weeks, so I would like to know if there is a calculated measure I can create that would return the work week instead of the aforementioned date format in my Date Measures.
Any help is appreciated.
Thanks!
Hi @Mark_Z
Try creating new calcualted measure using this formula:
CASE WHEN
Not IsEmpty([Measures].[Issue Planned Completion date])
THEN
"W"||
Format(
[Time.Weekly].[Day].DateMember(
[Measures].[Issue Planned Completion date]
).Parent.startdate, "ww")
END
It should return the week number for the week which has the Planned Completion date property value
Martins / eazyBI
Thanks Martins. I added the calculated measure but it seems to take away the original planned completion date when selecting as a measure. I had my original report filtered by issue type Feature. After applying new measure it unfilters that for some reason.
Before enabling calculated measure:
After enabling calculated measure:
Select measure “Issues with Planned Completion date” and filter the report by this column as explained here:
https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports#Createreports-Orderandfilterrowsbymeasurevalues
That will create a new context for the report, and you will see just the features that have Planned Completion date value.
Martins / eazyBI
Thanks for the pointer. Got that filtered. However, the new calculated measure shows ‘mmm dd yyyy’ as shown below.
Hey @Mark_Z
You must select the “text” format for the measure.
https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/calculated-measures#Calculatedmeasures-Formatting
In some cases, eazyBI can not detect the output format for the formulas automatically.
Martins / eazyBI
Thanks Martins, adjusting the formatting to text worked. However, I still have the problem of losing my filtering results when choosing this new measure. Even after applying the measure I cannot use any of my pages dimensions to re filter. Is there a way around this?
Try filtering the report also by the new column as explained here:
https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports#Createreports-Orderandfilterrowsbymeasurevalues
Martins / eazyBI
This helped, thanks again Martins.
One last item, how can i get the year to show along with my ww? Want it to show like ww20’23.
Try this code:
CASE WHEN
Not IsEmpty([Measures].[Issue Planned Completion date])
THEN
"ww"||
Format(
[Measures].[Issue Planned Completion date], "ww")
||"'"||
Format(
[Measures].[Issue Planned Completion date], "yyyy")
END
Martins / eazyBI
Thank you so much Martins. This worked perfectly.
