Forum Discussion
Multiply by filtered months
Hello!!
I have 3 tables:
1st - Table with all the moths in a year
2nd - Table with the actuals values by month by cost center
3rd - Monthly budget value by cost center
What I want to do is to compare the sum of the actual values with the sum of the budget multiplied by the same number of filtered months.
Example: I have filtered January and February and I get the sum of those 2 months in the actuals, lets say 10k. And then I want that, for those same cost centers (I have that connection alrealy stablished with table that have all cost centers) that the sum of the monthly budget value for those cost centers should be multiplied by 2 (jan and Feb). I already created a measure - "Delta" - that calculates the filtered months but I can only get the value for the budget if I select all the months. I even tried to create a column with my "Delta" measure but it would always return 12.
Please help 🙂
2 Replies
- amitchandak
Super User
Anonymous, Make sure you have a date table and create date in all tables using month.
Then you can use time intelligence
to get
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))or rolling
Rolling 2 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-2,MONTH))
You can use common date table and create measures
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
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. - Ahmedx
could you share your pbix-file? Or create an example file which reproduces your issue?
