Dynamic date calculation for getting current month data.

[liberty20]

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.

[Uspace87 · Accepted solution]

@liberty20 

My suggestion is to:

1) Create a nice Calendar Table that you can connect to you "Fact" table 

https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables

2) Use Time intelligence functions to calculate Sales MTD, QTD, YTD etc. Please find below and example:

Sales MTD = Calculate(
Sum(Sales_values),

DATESMTD(Date_Table[Day])
)

[liberty20]

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!