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.

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.

    • cristianml's avatar
      cristianml
      Post Prodigy
      7 years ago

      Hi Ashish_Mathur ,

       

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

       

      Best Regards !:)

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        7 years ago

        You are welcome.

    • cristianml's avatar
      cristianml
      Post Prodigy
      7 years ago

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