Forum Discussion

liberty20's avatar
liberty20
Frequent Visitor
3 years ago
Solved

Dynamic date calculation for getting current month data.

Hello experts,

I want to make my dates dynamic as per the current month.

Previously I was hard coding values as,

Example: 

MTD Rev = CALCULATE(SUM('Table'[Revenue]), 'Table'[Period]=DATE(2022,11,01))
Now, Instead of hardcode I have inserted filter condition as,
Example: 
MTD Rev  = CALCULATE(SUM('Table'[Revenue]),
FILTER('Table',MONTH('Table'[Period]) = MONTH(TODAY())),
FILTER('Table',YEAR('Table'[Period]) = YEAR(TODAY())))
 
Today() is working fine in my case but it is going to fail as month will change!
Because today will take system date and I need my formula to work according to the working days in a current month.
Kindly guide me to replace today() by working days as per current month.
 
Note: Max of date won't work as we are having future dates till 2024.

2 Replies

    • liberty20's avatar
      liberty20
      Frequent Visitor
      3 years ago

      Thank you Uspace87, above formula is working great for MTD calculations.

      Could you please suggest the same for non mtd calculations such as below:

      Fcast TPD =
      VAR FcastTotalTonnes = CALCULATE(SUM('Budget'[Tonnes]), 'Budget'[Date] = DATE(2022,12,01))
      VAR WDcurrentMonth = CALCULATE(AVERAGE('Budget'[WDAYS]),'Budget'[Date] = DATE(2022,12,01))
      return
      DIVIDE(FcastTotalTonnes,WDcurrentMonth,0)

       

      In above formula you can see 'Budget'[Date] = DATE(2022,12,01) is the static date which we need to manually change when every new month starts.

      Kindly suggest an idea to make above measure dynamic.

       

      Thank you!