Calculate Percentage Change from to Last Date in Power BI

[rgnxx]

I need to Calculate Percentage Change to Last Date.

For example, the date chosen now is June 2023. I need to calculate the percentage change of sales from dec 2021, to june 2023,

when the chosen date is May 2023, I need to calculate the percentage change of sales from dec 2021 to May 2023 and e.t.c

What I have now:

sales it is a measure that is calculated depending on the selected date:

sales = 
VAR sale = 
CALCULATE(
        SUM('sales'[sales]),
            FILTER(
                ALL('dates'[date_sale]),

                    'dates'[date_sale] >= MAX('dates'[start_period]) && 'dates'[date_sale] <= MAX('dates'[end_period]) 
                  )
        )
return sales

how i am calculating the percentage change:

% = 
VAR latestperiodbeg =
    CALCULATE (MAX('dates'[start_period]))
VAR latestperiodend=
    CALCULATE(MAX('dates'[end_period]))

VAR start_period= DATE(2021,01,01)
VAR end_period= DATE(2021,12,01)

VAR dec2021 =
    CALCULATE ( SUM('sales'[sales]), 'dates'[date_sale] >= star_period && 'dates'[date_sale] <= end_period 

VAR latest_date =
    CALCULATE ( SUM('sales'[sales]),'dates'[date_sale]>=latestperiodbeg && 'dates'[date_sale]<=latestperiodend)
RETURN
    DIVIDE ( latest_date, dec2021)

What i am expecting:

the dashboard above uses this measure:

max_date = IF(MAX(dates[date_sale])=DATE(2023,06,01),'Мeasures'[%],BLANK())

So, how can I dynamically change the date (2023,06,01) in above measure depending on the selected date value?

I tried to used:

var md=SELECTEDVALUE('dates'[date_sale])

var a=IF(MAX('dates'[date_sale])=md,'Measures'[%],BLANK())

return a

It doesnt work. Result is similar pic 1

[Anonymous · Accepted solution]

Hi @rgnxx ,

You can update the formula of measure as below and check if it can return the expected result:

max_date =
IF (
    SELECTEDVALUE ( dates[date_sale] ) = MAX ( 'slicerfield'[date] ),
    'Мeasures'[%],
    BLANK ()
)

If the above one can't help you get the expected result, please provide some raw data in your tables (exclude sensitive data) with Text format, your matrix fields setting. By the way, which field be applied on the date range slicer in the below screenshot? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards