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.
Hello,
I was asked to create a report that can show a comparison in costs to the same period last quarter, but I have no clue how.
So Month 1 of Q2 (April) needs to be compared to Month 1 of Q3 (July). I need to be able to show the difference between the two (both in real terms and in %).
Is there any way to do this?
Solved! Go to Solution.
Hi @Olia,
Based on my test, we can take the following steps to meet your requirement.
1.Enter the data and create a date table, and get the year and month column.
dimtime = CALENDARAUTO()
yymm = YEAR(dimtime[Date])*100+MONTH(dimtime[Date])
2.Create the measures as below, then we can get the result as we need.
previous = CALCULATE(SUM(Table1[sale]),DATEADD(dimtime[Date],-3,MONTH))
perc = IF(ISBLANK(SUM(Table1[sale])) || ISBLANK( [previous]),BLANK(),SUM(Table1[sale])-[previous])/SUM(Table1[sale])
For more details, please check the pbix as attached.
Regards,
Frank
Hi @Olia,
Based on my test, we can take the following steps to meet your requirement.
1.Enter the data and create a date table, and get the year and month column.
dimtime = CALENDARAUTO()
yymm = YEAR(dimtime[Date])*100+MONTH(dimtime[Date])
2.Create the measures as below, then we can get the result as we need.
previous = CALCULATE(SUM(Table1[sale]),DATEADD(dimtime[Date],-3,MONTH))
perc = IF(ISBLANK(SUM(Table1[sale])) || ISBLANK( [previous]),BLANK(),SUM(Table1[sale])-[previous])/SUM(Table1[sale])
For more details, please check the pbix as attached.
Regards,
Frank
Hi @Olia,
Does that make sense? If so, kindly mark my answer as a solution to close the case,
Best Regards,
Frank
Hi @v-frfei-msft Frank,
Thank you for bumping the post, I must have overlooked it.
I have tried your solution, but by applying it I get ino trouble on the main sheet. So I must be overlooking something. Can you please help?
I have created a Dimtime table like you suggested, and linked the Date part of it to the Date of the main table.
suddenly my Change GC measure, which was supposed to show the Change compared to Previous Month stopped working. It was using the Date from the main sheet (let's call it Sheet1)
do you have any clue as to what might have happened?
edit: if I replace 'Sheet1'[date].[date] by 'Dimtime'[date].[date] the measures give wrong or no results
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
104 | |
93 | |
83 | |
64 |