Solution for SAMEPERIODLASTYEAR with date filter applied

[renatobferrari]

Hi friends. Do someone could help me with this? I think there is a easy solution, but I haven't found it anywhere...

I'm trying to build a waterfall chart with variation vs LY in the data labels, but the calculation is not working because I'm filtering the date columns to it:

I'm using SAMEPERIODLASTYEAR to calculate LY sales, and it is working: 

But when I use the Sales vs LY in the data labels, it does not work. Does someone have some solution to use it correctly? The Idea is it calculate the variation vs LY, and also the variation in the 'division' of the waterfall chart.

Many thanks!

[renatobferrari · Accepted solution]

I've got it soluted, by a collegue, using IF and SELECTEDVALUE. Thanks, everybody"

[Anonymous]

Hi, @renatobferrari 

The possible reason why it doesn't work is that it lacks external context like a table visual, which doesn't work properly.

One workaround is to create a new measure, create a new var variable table within that measure, and use the summarize function to put the columns and measures of your visuals into this function. This virtual table will contain the same results as the table visual you are currently presenting. Then, the maxx function is used to work with the filter to extract the value corresponding to the current date according to the date of the x-axis of the waterfall chart.

Here are the possible DAX expressions:

Measure = 
VAR _table = summarize('table','table'[MonthDate],"Sales R$",[Sales R$],"Sales LY R$",[Sales LY R$],"Sales vs LY R$",[Sales vs LY R$])
VAR _waterfall_x_axis = SELECTEDVALUE('table'[MonthDate])
return maxx(FILTER(_table,'table'[MonthDate]=_waterfall_x_axis),[Sales vs LY R$])

You can check in DAX query to see if the result of the current summary output is the same as that shown in the current table visual.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[renatobferrari]

Thanks, it helped me to fix my calculation! But i'm facing another problem using this dynamic format, because now my labels are correct but for one column, it is calculating wrongly:

In this FYTD LY, the variation should be empty, once all the variations reflects TY/LY, and it is repeating the FYTD/FYTD LY (last column). Is there any solution, as bring it empty for the first column?

The dynamic formating I'm using is this one:

VAR vSalesAmt = [Sales R$]

VAR vVSly = [Sales R$ LY]

VAR FYTD_LY = CALCULATE([Sales R$],TB_FACT_SCANTRACK[FYTD]="FYTD LY")

VAR FYTD = CALCULATE([Sales R$],TB_FACT_SCANTRACK[FYTD]="FYTD")

VAR VS_LY = DIVIDE(FYTD, FYTD_LY, BLANK()) - 1

VAR vSalesAjust =

    """" &

        SWITCH(

            TRUE(),

            vSalesAmt >= 1000000000, FORMAT(vSalesAmt, "#,0,,,.00 Bi"),

            vSalesAmt >= 1000000, FORMAT(vSalesAmt, "#,0,,.00 Mi"),

            vSalesAmt >= 1000, FORMAT(vSalesAmt, "#,0,.00 K"),

            FORMAT(vSalesAmt, "#")

        )

    & """"

VAR vVSlyAjust =

    """" &

        FORMAT(VS_LY, "0.0%")

    & """"

RETURN

    vSalesAjust & " | " & vVSlyAjust