Fiscal year Formula created for YTD calculation

[cristianml]

Hi, 

I was using the following formula without any problem until this month. Numbers are not showing now.. Could you please help me to fix it ? The idea of the formula is to take from September of last year until last month, that, at this point in time will be  until 31 of Agust:

YTD Actual = CALCULATE([Actual],DATESBETWEEN('List Period'[Date],IF(MONTH(TODAY())>=9,DATE(YEAR(TODAY()),9,1),DATE(YEAR(TODAY())-1,9,1)),IF(MONTH(TODAY())=9,TODAY(),EDATE(TODAY(),-1))))

Thanks.

[Anonymous]

A simple hard code solution would look something like this. If you need a more dynamic solution, just let me know

YTD Cost 2019 = SUMX(FILTER(ALLSELECTED(Sales), DATESBETWEEN(Sales[Date], DATE(2019, 9, 1), DATE(2020, 8, 31))), Sales[Sales])

[cristianml]

Hi @Anonymous ,

I donot need a fixed DATE like DATE(2020, 8, 31. ..  I would like to modify the existing one just fixing this issue.

Thanks,

[cristianml]

Hi @Anonymous ,

As I mentioned before I donot need a Fixed value because it should work for future years. I think the clue is to modify the part related to:  >=9  but I donot have much knowledge about undertanding this logic in Dax.  

Thanks,

[Anonymous]

When I use your formula with data I have, that measure works perfectly. Is it possible that you simply do not have any September data in your model yet?

[cristianml]

Hi @Anonymous,

Yes, I have data from 2016 to 2025. So is another problem.  Not sure what would be the issue if for you works perfect.

But just to be clear: this MEASURE should take from September 2018 to August 2019 (if is correct the formula) but NOT September 2019.

Thanks,

[Anonymous]

I misunderstood your ask. The measure works currently to sum data from September-August. With it being September now, the measure is summing from 9/1/2019 through August 31, 2020. With your report data not being updated since early August (I can tell from the screenshot), I am guessing there is no current September data for your measure to work on which would explain the blank data on your card. 

Are you looking to have your measure maintain last Fiscal Years data, or have it transition into the new Fiscal Year that just began?

[cristianml]

Hi @Anonymous ,

YTD Actual = CALCULATE([Actual],DATESBETWEEN('List Period'[Date],IF(MONTH(TODAY())>=9,DATE(YEAR(TODAY()),9,1),DATE(YEAR(TODAY())-1,9,1)),IF(MONTH(TODAY())=9,TODAY(),EDATE(TODAY(),-1))))

This measure should sum from Sept 2018 to August 2019.  

Previous months it was working perfectly but for some reasaon now is not. i assume is caused by this part: >=9 

Example: last month (in August) this measure was summning from Sept 2018 to July 2019. 

In July was summing from Sept 2018 to June 2019 on so on.

What you see in the screenshot is just the last time I updated BUT I have DATA from 2016 to 2025 (all months) because is a Actual/forecast report. So it should work.

Thanks.

[v-frfei-msft]

Hi @cristianml ,

Update your measure to have a try please.

YTD Actual =
VAR a =
    MONTH ( TODAY () )
VAR d =
    IF (
        a >= 9,
        DATE ( YEAR ( TODAY () ), 9, 1 ),
        DATE ( YEAR ( TODAY () ) - 1, 9, 1 )
    )
VAR c =
    IF ( a = 9, TODAY (), EDATE ( TODAY (), -1 ) )
RETURN
    CALCULATE ( [Actual], DATESBETWEEN ( 'List Period'[Date], d, c ) )

[cristianml]

Hi @v-frfei-msft ,

For some reason is not working;  

 

Regards.