Cumulative Totals for different years and months for LOTS in powerbi

[poornadeepthi]

Hello,

I have been looking to create measure which shows Cumulative totals for LOTS and for all the months and years. I have created measure, It seems to be not working properly

To Date - RN Costs =

VAR CurrentYear = year([Current Data Date])

VAR Dates = DATE(CurrentYear,04,01)

VAR HistoricTotal = CALCULATE([RN Costs],Dim_Date[Date] < Dates)

VAR CurrentYearTotal = CALCULATE([RN Costs],Dim_Date[Date] = Dates)

VAR CurrentMonthNo = CALCULATE(max(Dim_Date[Fiscal Month No]),Dim_Date[Date] ==SELECTEDVALUE(Dim_Date[End of Month]))

VAR CurrentYearToDateTotal = (DIVIDE(CurrentYearTotal,12)) * CurrentMonthNo

RETURN  IF(SELECTEDVALUE(Dim_Lots[Lot]) <= 4 || SELECTEDVALUE(Dim_Lots[Lot]) = 8 ,HistoricTotal + CurrentYearToDateTotal,CALCULATE([RN Costs],ALL(Dim_Date[End of Month]),ALL(Dim_Date[Default To Date])))

I want to dynamically select end of month where it can show cumulative totals upto that specific month

Can someone help please

[poornadeepthi]

The value should be around 83M, but when I used the measure, it came around 102M. Is it because, I have FY should start from 01/04/ and end with 31/03? and please could you suggest on how could I map this measure to dates comparing from Date tables instead of using from RN table and also LOts come from LOT table not from RN table please?

[poornadeepthi]

I have used the below measure

_Measurecheck =

VAR _a = SELECTEDVALUE('Table 2'[end of month])

VAR _b = CALCULATE([Transformation Benefits Total],YEAR('Fact_Sch22_MoD Forecast NB'[Date]) < YEAR(_a))

VAR _c = DIVIDE(CALCULATE([Transformation Benefits Total],YEAR('Fact_Sch22_MoD Forecast NB'[Date]) = YEAR(_a)),12) *  MONTH(_a)

RETURN

 IF(ISBLANK(_a),[Transformation Benefits Total],_b + _c )

Transformation Benefits Total = sumx(VALUES(Dim_Lots[Lot]),[Transformation Benefits])

Instead of YEAR('Fact_Sch22_MoD Forecast NB'[Date]), can I use 

Dim_Date[Date]?

Dim_Date  measure 

Dim_Date = var FullCalendar = ADDCOLUMNS(CALENDAR([Start Date],[End Date]),"Month No",MONTH([Date]),"Year",YEAR([Date]),"MMM-YYYY", FORMAT([Date],"MMM-YYYY"))

    return

SUMMARIZE(FullCalendar,[Date], [Month No],[Year],[MMM-YYYY])

[v-zhouwen-msft]

Hi @poornadeepthi ,

Can you share your .pbix file?

[poornadeepthi]

Hi @v-zhouwen-msft ,

The data is for whole year. So to get the data the data in moths, I am just doing (total/12) for that year. So if I pick 31/03/2024, I would like to see sum(historic value)+(current year total/12)*max(Selected month)

[v-zhouwen-msft]

Hi @poornadeepthi ,

The Table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a table

Table 2 = SUMMARIZE(ADDCOLUMNS(CALENDAR(DATE(2021,1,1),DATE(2028,12,31)),"end of month",EOMONTH([Date],0)),[end of month])

2.Use the following DAX expression to create a measure

Measure = 
VAR _a = SELECTEDVALUE('Table 2'[end of month])
VAR _b = CALCULATE(SUM('Table'[RN Cost]),YEAR('Table'[Date]) < YEAR(_a))
VAR _c = DIVIDE(CALCULATE(SUM('Table'[RN Cost]),YEAR('Table'[Date]) = YEAR(_a)),12) *  MONTH(_a)
RETURN
 IF(ISBLANK(_a),SUM('Table'[RN Cost]),_b + _c )

3.Final output

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

[v-zhouwen-msft]

Hi @poornadeepthi , 

Regarding your question, please provide brief data and show the expected results in the form of pictures.

[poornadeepthi]

Hi,

I have below matrix in powerbi, which have multiple measures which has to show cumulative sums by LOT title. I have filter which shows end of months where user can pick any month and they can see cumulative totals upto that month. 

The thing is, I have data in years not in months/dates. 

Data

I have date calendar as well

Dim_Date = var FullCalendar = ADDCOLUMNS(CALENDAR([Start Date],[End Date]),"Month No",MONTH([Date]),"Year",YEAR([Date]),"MMM-YYYY", FORMAT([Date],"MMM-YYYY"))

    return

SUMMARIZE(FullCalendar,[Date], [Month No],[Year],[MMM-YYYY])

[v-zhouwen-msft]

Hi @poornadeepthi ,

I'd like to know if this is already the value for the whole year 2021 or just for this one month.

If it's a full year, then the time span of your data is years and the time span of the filter is months.

[poornadeepthi]

Hi @v-zhouwen-msft ,

Hi @v-zhouwen-msft 

The data is for whole year. So to get the data the data in moths, I am just doing (total/12) for that year. So if I pick 31/03/2024, I would like to see sum(historic value)+(current year total/12)*max(Selected month)

[poornadeepthi]

Hi @v-zhouwen-msft ,

Thanks for the quick response. But its not working in my dashboard. Lots and Dates should come from different tables as I need to sum up different values to get cumulatives for other measures. I need those values should come from different tables where I can use it for different measures and tables. I have added my sample data. Please see, when I click default, it has to show the cumulatives upto the previous months. Thanks

[poornadeepthi]

Hi @v-zhouwen-msft , Thanks for the measure. Its working. But the issue is,as the values in Financial years.01/04/2021 to 31/03/2022. It is summing up values in calendar year instead of financial year. Could you please suggest how to get the measure work in FY.

As you can see below, £83,539577 should come by 31/03/2024. But it is showing by 31/01/2024