Current YTD vs Last Month YTD Power BI Measure

[rane19]

Goal:

• 1 - show the change of every month compare with last YTD in the table form in only selected year
• 2 - show the change in scorecard

Data :

ind YEAR    MON Figures
1   2020    JAN 12321
2   2020    FEB 344
3   2020    MAR 5000
4   2020    APR 325
5   2020    MAY 747
1   2019    JAN 54091
2   2019    FEB 349
3   2019    MAR 789
4   2019    APR 901
5   2019    MAY 2356

Desire Result:

• YTD column is accumulating Figures within the year
• Change column is showing the change compare to last month YTD

Usage - Default filter as newest year = 2020 , user can select the specific single year to view the change in scorecard

ind YEAR    MON Figures YTD     Change
1   2020    JAN 12321   12321   0
2   2020    FEB 344     12665   344
3   2020    MAR 5000    17665   5000
4   2020    APR 325     17990   325
5   2020    MAY 747     18737   747
1   2019    JAN 54091   54091   0
2   2019    FEB 349     54440   349
3   2019    MAR 789     55229   789
4   2019    APR 901     56130   901
5   2019    MAY 2356    58486   2356

How can i achieve this ?

[v-jayw-msft · Accepted solution]

Hi @rane19 ,

Check the measures.

Measure = CALCULATE(SUM('Table'[Figures]),FILTER(ALLSELECTED('Table'),'Table'[YEAR]=SELECTEDVALUE('Table'[YEAR])&&'Table'[ind]<=MAX('Table'[ind])))

Measure 2 = 
var lastmonth = CALCULATE(SUM('Table'[Figures]),FILTER(ALLSELECTED('Table'),'Table'[YEAR]=SELECTEDVALUE('Table'[YEAR])&&'Table'[ind]<=MAX('Table'[ind])-1))
return
IF(ISBLANK(lastmonth),0,[Measure]-lastmonth)

Result would be shown as below.

Best Regards,

Jay

[rane19]

Amazing , it is exactly what i wanted.

[amitchandak]

@rane19 , My advice would be create a date table and join it with DAte table and use time intelligence

New column

Date = "01-" & [Month] & "-" & [year] // change Data type to date

measures

YTD Sales = CALCULATE(SUM(Table[Figures]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Table[Figures]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

diff = [YTD Sales] -[Last YTD Sales]

change = [YTD Sales] -SUM(Table[Figures])

[rane19]

Appreciate for the answer but what I am looking for is the accumulated total of Figures within each year at "YTD" column and the change (growth or drop) compare with last month's total and current month's total . Proposed answer looks Year to Year comparison