Same period last year - blank - line chart

[AnFroyman]

Hello, 

I want a line chart with a running total of this year, and a separate line of the running total of previous year.

A first issue I encounter is that with below DAX formula, there are 'gaps' in the LY running total (when the running total last year did not change day after day, this happens).

Running Total =

ROUND(CALCULATE(

    SUM([sales]),

    FILTER(

        ALLSELECTED('Calendar'[Date]),

        ISONORAFTER('Calendar'[Date], MAX('Calendar'[Date]), DESC)

    )

),2)

Running Total LY =

CALCULATE([Running Total],ALL('Calendar'[Date]),DATEADD('Calendar'[Date],-1,YEAR))

I work with a separate date table...

Eg, on the LY running total, there is a blank value for 08/05/2023, but in 2022 there was a value (the same as the day before)

The next issue is that the line graph of the last year running total is different from the real running total of last year (as seen in the 2022 running total orange line). This probably has something to do with the gaps in the LY running total?

[ribisht17]

hi @AnFroyman 

Can you share the sample dataset/pbix file so that we can test it ?

Regards,

Ritesh

[AnFroyman]

@ribisht17 how exactly do I share a file here?

[ribisht17]

Also, don't you think that the other values (non blank), even those are not correct ?

[ribisht17]

dropbox or google drive link

[AnFroyman]

@ribisht17 Here you go.

https://drive.google.com/file/d/1okhEtOHwr4SO5lAjeHoHQTvu1iNwDag3/view?usp=drive_link

[ribisht17]

Please try this DAX

Running Total LY without DateCalc =

CALCULATE(

    SUM('Facts'[Amount]),

    FILTER

    (

        ALLSELECTED('Calendar'[Date]),

        ('Calendar'[Date]) < MAX('Calendar'[Date])&&

        YEAR('Calendar'[Date]) < MAX('Calendar'[Date])

         )

    )

[AnFroyman]

Hi @ribisht17, it now looks like it is presenting the running total of yesterday instead of last year?

[ribisht17 · Accepted solution]

hi @AnFroyman 

Here you go,

Step 1

cal_running total this year =

CALCULATE (

    SUMX ( Facts,Facts[Amount]),

    FILTER (

        ALL ('Calendar'  ),

       'Calendar'[Day of Year]  <= MAX ( 'Calendar'[Day of Year] )

          && 'Calendar'[MonthNum]  <= MAX ( 'Calendar'[MonthNum])

            && 'Calendar'[Year] = SELECTEDVALUE('Calendar'[Year])

    )

)

Step 2

cal_running total last year =

CALCULATE (

    SUMX ( Facts,Facts[Amount]),

    FILTER (

        ALL ('Calendar'  ),

       'Calendar'[Day of Year]  <= MAX ( 'Calendar'[Day of Year] )

          && 'Calendar'[MonthNum]  <= MAX ( 'Calendar'[MonthNum])

            && 'Calendar'[Year] = SELECTEDVALUE('Calendar'[Year]) -1

    )

)

Looks like it is working

Ritesh

Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users 

[AnFroyman]

@ribisht17 lots of kudos for your effort, very glad with the result!

[ribisht17]

Glad that it helped!!

🙂

[ribisht17]

hi @AnFroyman 

Have you tried this  https://learn.microsoft.com/en-us/dax/sameperiodlastyear-function-dax ?

Regards,

Ritesh

Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users 

[AnFroyman]

@ribisht17 thanks for your reply. Indeed, the sameperiodlastyear function returns the same behaviour (blanks).