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 LastDa...
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.

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.

    • cristianml's avatar
      cristianml
      Post Prodigy
      7 years ago

      Ashish_Mathur ,

       

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

       

      Thanks

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        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.