How to display year end wise running total with measure

[harib]

Hi 

I Would like to display Running total like below .

Year             MJ_Values       Runnning Total

2017-11       10                    10

2017-12       10                    20

2018-01       05                    05

2018-01        20                   25

2018-01       05                    30

2019-01       06                    06

2019-02       10                    16

Running  Total should be end at every year end and it has to start at the new year beginings.

Measure i have used to get MJ Values

MJ Values = CALCULATE(sumX('Table',If( 'Table'[KPI]="RPN" && ('Table'[SEVERITY]) ="Major" ,1,0)))

If anyone knows please let me know 

Thanks in Advance

[v-shex-msft]

HI @harib,

I'd like to suggest you use date function to manly define filter range instead use time intelligence functions.

Time Intelligence "The Hard Way" (TITHW)  

Running total =
VAR currDate =
    MAX ( Table[Date] )
RETURN
    CALCULATE (
        SUM ( Table[MJ_Values] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Date] < currDate
                && YEAR ( Table[Date] ) = YEAR ( currDate )
        )
    )

If your field not stored valid date value, you can try to convert them to date in your expression before calculate.

Regards,

Xiaoxin Sheng

[amitchandak]

In case you have date column, then create a calendar and join the date with it. then you can use formulas like datesytd, totalytd

YTD Sales = CALCULATE(SUM(table[MJ Values]),DATESYTD(('Date'[Date]),"12/31"))

 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/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[harib]

@amitchandak 

YTD function not showing running total, It's just showing same values of Measure what we have. 

For better undstarnd of my requirment kindly go through below link

https://community.powerbi.com/t5/Desktop/How-to-display-selected-values-details-and-remaining-should...

Hope i't s helpful

Thanks in advance

[amitchandak · Accepted solution]

Try Like.

Critial YTD = CALCULATE([Critical],FILTER('Date','Date'[Date]<=maxx('Date',ENDOFYEAR('Date'[Date])))) 

[harib]

@amitchandak 

Can u send me any sample. I have tried your second method, but same measure values showing the visual.

Thanks 

[amitchandak]

Check Minor YTD :https://www.dropbox.com/s/hkv1jhe18gh3sq9/severity.pbix?dl=0

I removed few filter get data for more dates