DAX Running total measure - from start of financial year until selected month

[insandur]

Hi ,

I am trying to calculate sales value till the selected month from drop down and it should start from fiscal year month that is 
october . My Fy is from october to september of every year.

eg. if i select jan-24 in drop down i am expecting values from october-23 to Jan-24.

Thanks 

[Anonymous · Accepted solution]

Hi @insandur ,

Add a new table Month (Here month I use the number type, because it will be involved in the month of addition and subtraction, if you use the letter type please convert yourself) for slicer Month without any relationship:

And change the DAX into this:

Measure 2 = 
VAR _SelectMonth = SELECTEDVALUE('Month'[Month])
VAR _Year = YEAR(MAX('Table'[Date]))
VAR _Amount1 = 
CALCULATE(
    SUM('Table'[Amount]),
    FILTER(
        ALL('Table'),
        'Table'[Date] >= DATE(_Year - 1, 10, 1) && 'Table'[Date] < DATE(_Year, _SelectMonth + 1, 1)
    )
)
VAR _Amount2 = 
CALCULATE(
    SUM('Table'[Amount]),
    FILTER(
        ALL('Table'),
        'Table'[Date] >= DATE(_Year, 10, 1) && 'Table'[Date] < DATE(_Year, _SelectMonth + 1, 1)
    )
)
RETURN
IF(
    _SelectMonth < 10,
    _Amount1,
    _Amount2
)

And the final output is as below (On the Page2 of the .pbix):

2023: 10+20+30+40+50+60+70=280
2024: 30+40+50+60+70+80+90=420

2023: 10+20+30=60
2024: 30+40+50=120

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

[Anonymous · Accepted solution]

Hi @insandur ,

Maybe you can try this DAX:

Measure 3 = 
VAR thisyear = SUMX(FILTER(ALL('Table'), 'Table'[Date].[Year] = YEAR(MAX('Table'[Date])) && 'Table'[Date].[MonthNo] = SELECTEDVALUE('Month'[Month])), [Measure 2])
VAR lastyear = SUMX(FILTER(ALL('Table'), 'Table'[Date].[Year] = YEAR(MAX('Table'[Date])) - 1 && 'Table'[Date].[MonthNo] = SELECTEDVALUE('Month'[Month])), [Measure 2])
RETURN
IF(
    MAX('Table'[Date].[Year]) = MINX(ALL('Table'), 'Table'[Date].[Year]) && MAX('Table'[Date].[Year]) = MAXX(ALL('Table'), 'Table'[Date].[Year]),
    0,
    IF(
        lastyear = 0 || lastyear = BLANK() || thisyear = 0 || thisyear = BLANK(),
        0,
        (thisyear - lastyear) / lastyear
    )
)

However, because there are few samples in my data, the display effect is not very good. Maybe more data will show better results.

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

[Anonymous]

Hi @insandur ,

@NaveenGandhi Thanks for your concern about this case!

And @insandur , I build a sample data myself:

And I add a new table to build a slicer without any relationship:

Then I use this DAX to create a measure:

Measure = 
VAR _SelectMonth = MONTH(SELECTEDVALUE(Slicer[Slicer]))
VAR _SelectYear = YEAR(SELECTEDVALUE(Slicer[Slicer]))
VAR _Amount1 = 
CALCULATE(
    SUM('Table'[Amount]),
    FILTER(
        ALL('Table'),
        'Table'[Date] >= DATE(_SelectYear - 1, 10, 1) && 'Table'[Date] < DATE(_SelectYear, _SelectMonth + 1, 1)
    )
)
VAR _Amount2 = 
CALCULATE(
    SUM('Table'[Amount]),
    FILTER(
        ALL('Table'),
        'Table'[Date] >= DATE(_SelectYear, 10, 1) && 'Table'[Date] < DATE(_SelectYear, _SelectMonth + 1, 1)
    )
)
RETURN
IF(
    _SelectMonth < 10,
    _Amount1,
    _Amount2
)

And the final output is as below:
If I choose 2023.4 :

10+20+30+40+50+60+70=280.

If I choose 2024.4 :

30+40+50+60+70+80+90=420.

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

[insandur]

Thank you so much Tino,

 This was great for single fiscal year, is it bpossible for all fiscal years .
eg if i select April  month from drop down regardless of year (year can removed from filter ) how can i get running total till selected month from the starting month of fiscal year i.e october for all the previous fiscal year.

as you can see in below chart my numbers show for current FY and prev FY numbers are not correct.

[Anonymous · Accepted solution]

Hi @insandur ,

Add a new table Month (Here month I use the number type, because it will be involved in the month of addition and subtraction, if you use the letter type please convert yourself) for slicer Month without any relationship:

And change the DAX into this:

Measure 2 = 
VAR _SelectMonth = SELECTEDVALUE('Month'[Month])
VAR _Year = YEAR(MAX('Table'[Date]))
VAR _Amount1 = 
CALCULATE(
    SUM('Table'[Amount]),
    FILTER(
        ALL('Table'),
        'Table'[Date] >= DATE(_Year - 1, 10, 1) && 'Table'[Date] < DATE(_Year, _SelectMonth + 1, 1)
    )
)
VAR _Amount2 = 
CALCULATE(
    SUM('Table'[Amount]),
    FILTER(
        ALL('Table'),
        'Table'[Date] >= DATE(_Year, 10, 1) && 'Table'[Date] < DATE(_Year, _SelectMonth + 1, 1)
    )
)
RETURN
IF(
    _SelectMonth < 10,
    _Amount1,
    _Amount2
)

And the final output is as below (On the Page2 of the .pbix):

2023: 10+20+30+40+50+60+70=280
2024: 30+40+50+60+70+80+90=420

2023: 10+20+30=60
2024: 30+40+50=120

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

[insandur]

Hi Dino,

Thank you so much, this works perfect.
I also want to see the % change as Line in the chart.
  Eg if i select April in drop down. Changes fromFY23 (sum 50)  april to FY24 April (70)

changes would be 40% .

I tried DATESINPERIOD metho but i get wrong values.

Thanks in advance.

w

[Anonymous · Accepted solution]

Hi @insandur ,

Maybe you can try this DAX:

Measure 3 = 
VAR thisyear = SUMX(FILTER(ALL('Table'), 'Table'[Date].[Year] = YEAR(MAX('Table'[Date])) && 'Table'[Date].[MonthNo] = SELECTEDVALUE('Month'[Month])), [Measure 2])
VAR lastyear = SUMX(FILTER(ALL('Table'), 'Table'[Date].[Year] = YEAR(MAX('Table'[Date])) - 1 && 'Table'[Date].[MonthNo] = SELECTEDVALUE('Month'[Month])), [Measure 2])
RETURN
IF(
    MAX('Table'[Date].[Year]) = MINX(ALL('Table'), 'Table'[Date].[Year]) && MAX('Table'[Date].[Year]) = MAXX(ALL('Table'), 'Table'[Date].[Year]),
    0,
    IF(
        lastyear = 0 || lastyear = BLANK() || thisyear = 0 || thisyear = BLANK(),
        0,
        (thisyear - lastyear) / lastyear
    )
)

However, because there are few samples in my data, the display effect is not very good. Maybe more data will show better results.

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

[insandur]

Thank you Dino,

This is perfectly working.
However whenever i select months october, Nov & dec my FY shows previous year FY (My FY starts from October to sept. FY 24 starts from october and that 30 should be of FY 24 & 10 should be of FY 23

eg. if i select October i get Fy 24 blank and shows for FY 23 which should be FY 24.

[Anonymous · Accepted solution]

Hi @insandur ,

Oh I forgot to mention that in my test file I didn't create a FY column, the x-axis of the visual object I placed directly the year of the date column in the test data. You can add a FY column to my test data to indicate which fiscal year the corresponding row of dates belongs to, and then just place that column as the x-axis in the visual object.

Best Regards,
Dino Tao

[insandur]

Hi Dino, 
Thank you so much for above solution, i have another situation here,

Instead of Month i have year and quarter in dropdown for selection. if i select 20243 i,e 2024 and quarter 3,  all the FY sales value should give me all Q3 values for FY  andif 20242 is selected i,e Q2 visual should be all Q2 values for all FY years.

[NaveenGandhi]

Hi @insandur 

Provide sample file, sample data and sample output to understand the problem better.

Regards,
NG