Sum of all previous amounts up to the end of the selected period

[Patrick_Murphy]

Hi everyone,

i am trying to find a way to sum all previous amounts up to the end of the selected fiscal year or fiscal quarter.

In the examples below, i tried to reproduce what i'm trying to do.

I have a date table with fiscal years and quarters that is linked to my fact table that contains the net amounts i want to sum.

I want to filter the data in my report to show the current years data but for this measure, i would like it to sum all the past entries up to the end of the selected year in the slicer.

So, 2023 would sum up all past entries right up to the end of the 2023.

2024, all past entries up to the end of 2024, and so on.

There is probably an easy solution for this but i'm not a DAX expert. So any help would be greatly appreciated.

Thanks.

[ExcelMonke]

Hello, 

Consider the following measure: 

natAmountTD = 
VAR _MinDate = [your min date here]
VAR _MaxDate = SELECTEDVALUE(DateTable[FiscalYear])

RETURN
CALCULATE(SUM(FactTable[natAmount]),DATESBETWEEN(DateTable[FiscalYear],_MinDate,_MaxDate))

[Patrick_Murphy]

Thanks for the reply !

I tried this but it doesn't work since my fiscal year isn't a date, it's a text ex: FY2024. So the DATESBETWEEN function doesn't accept it.