Dax Formula Between Dates

[cristianml]

Hi,

I need to fix this formula. It doesn´t work. 

YTD MTD Actual Cost = CALCULATE([Actual Cost], // Sum Amount

VAR FirstFiscalMonth = 9 -- Set the first month of the fiscal year

VAR LastDay = MAX ( 'List Period'[Date] )

VAR LastMonth = MONTH ( LastDay )

VAR LastYear = YEAR ( LastDay )

- IF ( LastMonth < FirstFiscalMonth, 1 )

Return

DATESBETWEEN('List Period'[Date], // Based on Dates Between

Date( LastYear, FirstFiscalMonth, 1 ),MONTH(EDATE(today(),-1))

))

Could you Help me to fix it ?

Thanks and regards.

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

Measure1 = CALCULATE([Actual Cost],DATESBETWEEN('Calendar'[Date],IF(MONTH(TODAY())>=9,DATE(YEAR(TODAY()),9,1),DATE(YEAR(TODAY())-1,9,1)),IF(MONTH(TODAY())=9,TODAY(),EDATE(TODAY(),-1))))

Hope this helps.

[cristianml]

Hi,

I would like to modify the START DATE of this Formula adding a VAR where I can set the MONTH of last Year..

MTD Actual Cost = CALCULATE([Actual Cost], // Sum Amount
DATESBETWEEN('List Period'[Date], // Based on Dates Between
Date(YEAR(EDATE(today(),-1)),MONTH(EDATE(today(),-1)),1), // Date having Year = Today -1 Month / Month = today -1 Month, and Day = 1
EOMONTH(today(),-1))) // End Of Month effective today() -1 month

I would like to put/use something like this into the Formula:

VAR FirstFiscalMonth = 9 -- Set the first month of the fiscal year

Thanks,

[Ashish_Mathur]

Hi,

Please describe your question.  From what i can understand, you want to add run the Actual cost measure calculation between September (that is where your FY start from) till the previous month (based on the Today's date).  So if Today is June 1, 2019, then you want to consider the period as September 1, 2018 to May 1, 2019.

Am i correct?

[cristianml]

Hi @Ashish_Mathur ,

Yes, I'm trying to calculate the measure from September of Last Year (September'18) to previous Month (May'19).  But I don't want to put 2018-09-01 or any fixed date as this model should work next year, so I'm trying to create a measure that doesn´t need any modification the following years.

Thanks.

[cristianml]

@Ashish_Mathur ,

One additional comment: To be clear this sould be to entire month, so to May 31.

Thanks

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

Measure1 = CALCULATE([Actual Cost],DATESBETWEEN('Calendar'[Date],IF(MONTH(TODAY())>=9,DATE(YEAR(TODAY()),9,1),DATE(YEAR(TODAY())-1,9,1)),IF(MONTH(TODAY())=9,TODAY(),EDATE(TODAY(),-1))))

Hope this helps.

[cristianml]

Hi @Ashish_Mathur ,

Thanks again for your measure ... Now I would like to do another measure Similar to this but instead of begining from September Last year I would like to consider ALL previous dates till Last month May'19 (or EndOfMonth) from my model .. per example My model have information since July 2016  so I would like to calculate from July 2016 to May'19 with a similar measure you provided to me: 

Actual Cost = CALCULATE([Actual Cost],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))))

I assume it would be the same formula but changing a few things only, right ? Do I need to post this as a new Subject ?

Thanks again in advance :

Regards.

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

Actual Cost since inception = CALCULATE([Actual Cost],DATESBETWEEN('List Period'[Date],MINX(ALL('Calendar'[Date]),'Calendar'[Date]),IF(MONTH(TODAY())=9,TODAY(),EDATE(TODAY(),-1))))

Hope this helps.

[cristianml]

Hi @Ashish_Mathur ,

Finally it worked.. I only removed the September part leaving the formula like this: 

CTD Actual Cost = CALCULATE([Actual Cost],DATESBETWEEN('List Period'[Date],MINX(ALL('List Period'[Date]),'List Period'[Date]),EDATE(TODAY(),-1)))

Thanks ! Regards.

[cristianml]

Hi @Ashish_Mathur ,

I tried the measure but is not working..  Is showing same amount that Previous one (YTD). I think the problem is that the following part needs to be removed but not sure how:

,IF(MONTH(TODAY())=9

Thanks,

[cristianml]

Hi @Ashish_Mathur ,

Works perfect !  I was trying to find this measure long time ago 🙂 Thanks a lot !! 

Best Regards !:)

[Ashish_Mathur]

You are welcome.