Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I am currently trying to build a dynamic report where users can select a time period and see sales numbers for the current time period as well as the previous period. To assist users with this I have also tried to pre-define some time periods to make it easier to filter. My set up is as follows with three relevant tables for this report:
For DatePeriod I have used the following code to create pre-defined reporting periods:
DatePeriod = UNION ( ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Dates' ; DATESBETWEEN('Dates'[Date];EOMONTH(TODAY();-2)+1;EOMONTH(TODAY();-1)) ); 'Dates'[Date]);"Period";"Previous Month") ; ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Dates' ; DATESBETWEEN('Dates'[Date];EOMONTH(TODAY();-1)+1;EOMONTH(TODAY();0)) ); 'Dates'[Date]);"Period";"Current month") ; ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Dates'); 'Dates'[Date]);"Period";"Overall") )
This is what the report looks like without a filter being set:
Looks as I would expect it to look. However if I set a filter, for example "Current Month", this happens:
The value for the previous time period is no longer being displayed. I should also add that if I use a direct filter on my Date table and select a period in there via the timeline or the relative date capabilities, the previous time period gets displayed properly.
Here is how Sales PTP is defined:
Sales PTP = CALCULATE(SUM(SalesCalculated[Sales Amount]);SAMEPERIODLASTYEAR(Dates[Date]))
I am assuming that my approach with pre-defined time periods is somehow not playing nice with the SAMEPERIODLASTYEAR() function.
EDIT: I also tried this formula. The result was that Sales PTP displayed the same value as sales when a time period was selected.
Sales PTP = CALCULATE(SUM(SalesCalculated[Sales Amount]);FILTER(ALL(Dates[Date]);SAMEPERIODLASTYEAR(Dates[Date])))
@Anonymous ,
Could you please share some sample data and give the expected result?
Regards,
Jimmy Tao
User | Count |
---|---|
90 | |
85 | |
65 | |
62 | |
58 |
User | Count |
---|---|
147 | |
113 | |
95 | |
81 | |
71 |