Forum Discussion

cristianml's avatar
cristianml
Post Prodigy
7 years ago
Solved

Dax Formula Between Dates

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.
Need Help
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    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.

11 Replies

  • cristianml's avatar
    cristianml
    Post Prodigy
    7 years ago

    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's avatar
    Ashish_Mathur
    Super User
    7 years ago

    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's avatar
      cristianml
      Post Prodigy
      7 years ago

      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.