Filtering and comparing by previous number and current number

[Anonymous]

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. 

[TomMartens]

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

[Anonymous]

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!

[TomMartens]

Hey @Anonymous ,

just to make sure that I understand your requirement 100%, I tried to phrase a business rule below:

• The previous value should not be calculated for Country:DK and Calendar Year Week:202031.
• Just one previous value should be calculated for each country, just for the most recent value (max) in the column Calendar Year Week

Regards,

Tom

[Anonymous]

Hey @TomMartens 

So, I just need to calculate the difference between the most current value and the previous value:

1. for example in DK, the most current value is 202032 and the value of Total Potential is 233438 and I have to calculate the difference with 202031 which is 46093479. (Most current value 233438  - Previous value 46093479);
2. For FI, for example, the most current value is 202032, and the value of Total Potential is 1643682 and I have to calculate the difference with the previous value 202026, 24699009 (total potential). (Most Current value 1643682 - Previous value 24699009)

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 !

[TomMartens · Accepted 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

[Anonymous]

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 ! 

[Anonymous]

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!

[TomMartens]

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