Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |