Monthly running total to compare year to year

[nawlins76]

I am trying to create a Line and Bar chart that will represent a running total from 2019(Line) and 2020(Bar) over the same months. However, the 2019 data from Jan-Aug is displayed as the running total from Dec 2019.

Here are my formulas and screeshots below:

2019  = CALCULATE(

            COUNT(PS_Data[Employee ID]),

FILTER(

ALLSELECTED(PS_Data),

PS_Data[Late/Not Late] = "Early" &&

PS_Data[Action Date]<= MAX(PS_Data[Action Date])&& PS_Data[Action Date].[Year] = 2019))

2020  = CALCULATE(

            COUNT(PS_Data[Employee ID]),

FILTER(

ALLSELECTED(PS_Data),

PS_Data[Late/Not Late] = "Early" &&

PS_Data[Action Date]<= MAX(PS_Data[Action Date])&& PS_Data[Action Date].[Year] = 2020))

[amitchandak]

@nawlins76 , if you date or create a new column with help from this video -https://www.youtube.com/watch?v=cJqgphIHXz8

They try time intelligence and date table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Next month value =  CALCULATE(sum('table'[total hours value]),nextmonth('Date'[Date]))
previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))
Next to next  month value =  CALCULATE(sum('table'[total hours value]),nextmonth(dateadd('Date'[Date],1,MONTH)))
previous to previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth(dateadd('Date'[Date],-1,MONTH)))
Last year same month value =  CALCULATE(sum('table'[total hours value]),previousmonth(dateadd('Date'[Date],-12,MONTH)))

Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[nawlins76]

@amitchandak Thank you, but using the date table isn't giving the results I'm looking for... essentially, I need to 2019 data to show as Null or Blank or even 0 during the 2020 months. see chart below for desired output:

[v-yingjl · Accepted solution]

Hi @nawlins76 ,

You can modify your [2019] measure like this:

2019 = 
CALCULATE (
    COUNT ( PS_Data[Employee ID] ),
    FILTER (
        ALL ( PS_Data ),
        'PS_Data'[Late/Not Late] = "Early"
            && 'PS_Data'[Action Date]
                <= MAXX (
                    FILTER ( 'PS_Data', 'PS_Data'[Action Date].[Year] = 2019 ),
                    [Action Date]
                )
    )
)

Atttached a sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

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

[nawlins76]

Thank you VERY much! This solved my issue. Just had to change the ALL to ALLSELECTED.