Forum Discussion
Dynamic date calculation for getting current month data.
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])
)
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])
)
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!