DAX for Cumulated total required Product and Month wise

[PijushRoy]

Hi Team,

I have DataTable and Calendar table. In DataTable, I don't have a date column.
I need to show Months wise and Product wise Cumulative sales total per year.

My fiscal year from JULY to JUNE

I have created another Calendar Table for Cumulative total and relationship.

In the below screenshot, you can find wrong data is coming

The calculation I am using for Specific year wise

FY21_Cumm_Sales = 
VAR _Sales = CALCULATE(SUM('DataTable'[SalesFY21]),
            FILTER(ALLEXCEPT('DataTable','DataTable'[Product]),
            'DataTable'[Fiscal_MonthNo] <= MAX('DataTable'[Fiscal_MonthNo])))
VAR _SelectedMonth = SELECTEDVALUE(Calnedar[Calendar_MonthNo])
RETURN
CALCULATE(_Sales, CROSSFILTER(Calnedar[Fiscal_MonthNo],'DataTable'[Fiscal_MonthNo],None),
FILTER('Calender for Commulative','Calender for Commulative'[Fiscal_MonthNo] >= _SelectedMonth))

Please find the PBIX file. and Expected Result file in Excel

YOUR HELP IS HIGHLY APPRECIATED, THANKS

[negi007 · Accepted solution]

@PijushRoy I have created a date field in your both date and data table to link the table

and then below measure can help to calc ytd value

TotalYTD_Vol = TOTALYTD(SUM('DataTable'[Amount]),Calnedar[Date],ALL(Calnedar[Date]),"Jun 1")

[PijushRoy]

Thanks @DimaMD @negi007 
Both solutions are working

[negi007 · Accepted solution]

@PijushRoy I have created a date field in your both date and data table to link the table

and then below measure can help to calc ytd value

TotalYTD_Vol = TOTALYTD(SUM('DataTable'[Amount]),Calnedar[Date],ALL(Calnedar[Date]),"Jun 1")

[DimaMD]

Hi @PijushRoy  see the attached file, I have made changes to your measure

FY23_Cumm_Sales = 
VAR _Sales = CALCULATE(SUM('DataTable'[SalesFY23]),
            FILTER(ALLEXCEPT('DataTable','DataTable'[Product]),
            'DataTable'[Fiscal_MonthNo] <= MAX('DataTable'[Fiscal_MonthNo])))
VAR _SelectedMonth = MIN('DataTable'[Fiscal_MonthNo])
RETURN
CALCULATE(_Sales, 
FILTER('DataTable','DataTable'[Fiscal_MonthNo] <= _SelectedMonth))

[PijushRoy]

Hi @DimaMD 

Yes, it is working when taking slicer value from DataTable.
But I can't take slicer form DataTable because I have other visual in same page and common slicer from Calender table.

I am facing same issue, any way to solve this?

Thanks for your reply

[DimaMD · Accepted solution]

HI @PijushRoy and @negi007 
There are two solutions in the attached file, mine and @negi007 , take a look and decide which one suits you better. 
You can accept both my decision and @negi007 

[negi007]

@PijushRoy did you try the solution suggested by me.