Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I have this CALENDARYEARWEEK column:
CALENDARYEARWEEK | COUNTRY |
202026 | FI |
202032 | FI |
202026 | DK |
202031 | DK |
202032 | DK |
and baiscally I want to compare a certain value for the previous week and the current week in that column: So, for DK, I'm want to compare the week 32 with 31 and for FI I want to compare the week 26 with 32?
For DK:
prev = (CALCULATE(SUM(AMOUNT), FITLER(SALES, previous week(202031))
cur = (CALCULATE(SUM(AMOUNT), FITLER(SALES, current week (202032))
RETURN
cur - prev
And the comparison between the two weeks will give the percentage difference.
Is it possible to do this ? and create this dynamic? for example when inserting week 33, compare with 32?
Thanks for your attention,
Kind regards.
Solved! Go to Solution.
Hey @Anonymous ,
I assume this is the measure you are looking for:
previous value =
SUMX(
VALUES( Fact_Potential[Country] )
, var maxValue = CALCULATE( MAX( 'Fact_Potential'[Calendar Year Week]) , ALL( Fact_Potential[Calendar Year Week] ) )
return
IF( MAX( 'Fact_Potential'[Calendar Year Week] ) < maxValue
, BLANK()
, CALCULATE(
LASTNONBLANKVALUE( 'Fact_Potential'[Calendar Year Week] , [Total Potential] )
, FILTER( ALL( Fact_Potential[Calendar Year Week] ) , 'Fact_Potential'[Calendar Year Week] < MAX( 'Fact_Potential'[Calendar Year Week] ) )
)
))
Regards,
Tom
Hey @Anonymous ,
this measure
previous value =
SUMX(
VALUES( Fact_Potential[Country] )
, CALCULATE(
LASTNONBLANKVALUE( 'Fact_Potential'[Calendar Year Week] , [Total Potential] )
, FILTER( ALL( Fact_Potential[Calendar Year Week] ) , 'Fact_Potential'[Calendar Year Week] < MAX( 'Fact_Potential'[Calendar Year Week] ) )
)
)
calculates the previous value base on the sample file you provided. Without using the DimDate table.
Depending on the size of your dataset, this approach might be sufficient. In regards to the article, I call this the naive approach.
A screenshot:
Hopefully, this is what you are looking for.
Regards,
Tom
Hi @TomMartens ,
I used that measure too from your article.
It is almost what I intend, but I just want, in the case of DK, getting the values of 202031 (not 202026) to compare with the most current value 202032, that is, I always want to compare with the most current, in this case it is 202032, with the previous 202031, and in the case of FI and FR it is to compare 202032 with 202026.
And in the future if I insert the value 202033, I'll want to compare with 202032.
A screenshot:
Maybe I can create a Max Calendar year filter like a:
CASE WHEN
[CALENDARYEARWEEK] = (SELECT MAX([CALENDARYEARWEEK]) FROM [FACT_POTENTIAL])
THEN 1
ELSE 0
END AS [IsMaxCalendar]
and put it in Filter Pane, but I’m afraid if I do that, I’ll just get the values for 202032.
Once again thank you so much and kind regards!
Hey @Anonymous ,
just to make sure that I understand your requirement 100%, I tried to phrase a business rule below:
Regards,
Tom
Hey @TomMartens
So, I just need to calculate the difference between the most current value and the previous value:
It is always the difference between the most current value and the previous value of the current value.
When a new calendar year week value 202033 is entered, it will calculate the difference in total potential between 202033 and 202032. For example DK (Most current value - New previous value 233438 (202032))
Let me know if I explained well,
Thank you and kind regards !
Hey @Anonymous ,
I assume this is the measure you are looking for:
previous value =
SUMX(
VALUES( Fact_Potential[Country] )
, var maxValue = CALCULATE( MAX( 'Fact_Potential'[Calendar Year Week]) , ALL( Fact_Potential[Calendar Year Week] ) )
return
IF( MAX( 'Fact_Potential'[Calendar Year Week] ) < maxValue
, BLANK()
, CALCULATE(
LASTNONBLANKVALUE( 'Fact_Potential'[Calendar Year Week] , [Total Potential] )
, FILTER( ALL( Fact_Potential[Calendar Year Week] ) , 'Fact_Potential'[Calendar Year Week] < MAX( 'Fact_Potential'[Calendar Year Week] ) )
)
))
Regards,
Tom
Hey @TomMartens ,
Yes, this is what I want !! Thank you very much for all your support, you helped a lot.
Stay safe and kind regards !
Hi @TomMartens
Thank you very much for all support, your article is very good!, I'll try to follow the steps and create the solution.
However, I leave the link that contains the csv file that creates the sample data and the pbix file if you want to take a look too:
https://1drv.ms/u/s!AnhB73hnka5JgXeHyZ-UhZ3aZXqP?e=SbTydX
Once again, thank you and kind regards!
Hey @Anonymous ,
basically, it's possible, but it's not that simple to determine the previous week. And using a more obvious approach becomes slow on larger datasets, the sample files also contain measures for the more obvious solutions, leveraging the DAX functions LASTNONBLANKVALUE.
Here I describe a different approach to determine a previous value: https://www.minceddata.info/2020/11/02/the-previous-value/
If you need more help to adapt the approach to your data, prepare a pbix file that contains sample data but still reflects your data model. Upload the file to onedrive or dropbox and share the link. If you use an xlsx to create the sample data, share the file as well.
Regards,
Tom
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
73 | |
60 | |
58 |