Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I have a forecast rate Table to forecast the current month count based on last month.
For example, 2021-02 is 129 count and this month is 2021-03. The forecast count should be 129 * (1+10.5%) = 143.
How to create a measure to calculate if Month(Today()) = Rate[Month], then PreviousMonth(Date) * Rate?
Here is the sample.
Solved! Go to Solution.
Hi @PBI_newuser ,
Please follow these steps:
1. Add a MonthNumber column to Rate table:
MonthNumber =
MONTH ( CONVERT ( [Month] & " 1", DATETIME ) )
2. Add a Rate column to Data table.
Rate =
VAR _month =
IFERROR ( CONVERT ( RIGHT ( [Period], 2 ), INTEGER ), BLANK () )
VAR _m =
IF ( _month = 12, 0, _month )
RETURN
LOOKUPVALUE ( 'Rate'[Rate], 'Rate'[MonthNumber], _m + 1 )
3. Create measure:
Measure =
[ID Count]
* ( MAX ( 'data'[Rate] ) + 1 )
The final output is shown below:
Please take a look at the pbix file here.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @PBI_newuser ,
Please follow these steps:
1. Add a MonthNumber column to Rate table:
MonthNumber =
MONTH ( CONVERT ( [Month] & " 1", DATETIME ) )
2. Add a Rate column to Data table.
Rate =
VAR _month =
IFERROR ( CONVERT ( RIGHT ( [Period], 2 ), INTEGER ), BLANK () )
VAR _m =
IF ( _month = 12, 0, _month )
RETURN
LOOKUPVALUE ( 'Rate'[Rate], 'Rate'[MonthNumber], _m + 1 )
3. Create measure:
Measure =
[ID Count]
* ( MAX ( 'data'[Rate] ) + 1 )
The final output is shown below:
Please take a look at the pbix file here.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Try with a date table
Forecast = CALCULATE([ID Count],DATESMTD(dateadd('Date'[Date],-1,MONTH))) * (1+CALCULATE([Rate],DATESMTD(dateadd('Date'[Date],-1,MONTH))))
or
Forecast = CALCULATE([ID Count],DATESMTD(dateadd('Date'[Date],-1,MONTH))) * (1+CALCULATE([Rate],DATESMTD(dateadd('Date'[Date],-1,MONTH)))/100)
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
88 | |
82 | |
65 | |
64 | |
58 |
User | Count |
---|---|
171 | |
112 | |
110 | |
72 | |
72 |