issue in day to day variance % with non consecutive dates

[arielfmdp]

Hi team, I'm having a headache with this problem, I need to calculate a column with variance in percentage to previous date, BUT with non consecutive dates.

I'm getting this using

Var%PreviousDate =

((SUM('cnv rends-patrim-varcp'[CantCPActual]) /

  CALCULATE(

     SUM('cnv rends-patrim-varcp'[CantCPActual]);

     PREVIOUSDAY('cnv rends-patrim-varcp'[fecha])

))-1)*100

For example, the previous date for 19/03-16/03 doesn't work because the formula is using 18/03 instead of 16/03.

I should be able to get the spreadsheet aproach, look:

 Well, that's all, I hope someone can help, I didn't find this issue on the forums.

Regards,

Ariel

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[arielfmdp]

Sorry, I'm new   here is the link to the test pbix

https://drive.google.com/open?id=1SpXqIgStbgwuvwLRhloCw_30aJ7_qww8

[Vvelarde · Accepted solution]

@arielfmdp

Hi try wiyh:

Var%PreviousDate =
VAR fdate =
    SELECTEDVALUE ( Tabla1[Date] )
VAR ff =
    CALCULATE ( MAX ( Tabla1[Date] ); Tabla1[Date] < fdate )
RETURN
    (
        (
            SUM ( Tabla1[CantCPActual] )
                / CALCULATE (
                    SUM ( Tabla1[CantCPActual] );
                    FILTER ( ALL ( Tabla1 ); Tabla1[Date] = ff )
                )
                - 1
        )
            * 100
    )

Regards

Victor

Lima - Peru

[arielfmdp]

Thank you very much @Vvelarde, it worked perfectly!

I'm still have a lot to learn about DAX.

[Greg_Deckler]

To get the previous day, you want to find the MAX of all dates that are less than the current date. Tough to be more specific without data pasted in a way that can be copied and pasted easily (non-image). But, these two links might help, see what I do with MTBF in this article:

See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

Also, see this article:

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[arielfmdp]

sorry, here is the link to the test pbix

https://drive.google.com/open?id=1SpXqIgStbgwuvwLRhloCw_30aJ7_qww8