Need a better way to handle multiple QTDs and MTDs versus actuals

[Anonymous]

Hello, 

I am creating a report that shows current MTD revenue, MTD budget, and then MTD Revenue vs Budget (as a percent).    And same for QTD. 

For the MTD vs Budget, I am just using this simple formula   (and same for QTD).    In the example, the budget amount is $8,000,000.      MTD Rev vs Plan = [MTD Revenue]/8000000

The problem I need to solve, is that I don't want to have to manually update each new month, or each new quarter  and/or create 12 separate measures (one for each month).    I just need the formulas to recognize the current month (or quarter) and be able to pull from the budget or revenue table accordingly in much more elegant fashion.     I know there is a way. 

Please help!   Thanks 😀

[amitchandak]

Are you looking for initializing page to today's date?

https://www.youtube.com/watch?v=lkHFpmA4SJ4

[Anonymous]

Hi @amitchandak

I'm not really clear on your question.   Here is a small part of my report.    For the MTD revenue, I could  just use the relative date feature in Power Bi and have the date dynamically adjust.     But for QTD and for the QTD and MTD vs Plan, I am not sure how to get these formulas to dynamically adjust once we enter a new month (or a new quarter).    

I don't want to have to create a MTD vs Budget for Jan, Feb, Mar, Apr, May....etc.....and same for Q1, Q2, ....

I already have a monthly budget table, and a quarterly budget table.   Somehow I would like a dynamic formula to go in, for example, and grab the February budget number and compare it against the Feb budget plan...without manually updating the previous month's (Jan) formula.   

[amitchandak]

Not sure, how are you calculating. But this how I . And they are dynamic

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))


QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date])))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year)))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year))))

[Anonymous]

That is great information, and will certainly try and use some of this in my reports.  

However, unfortunately this still does not really address my issue.   

I have a date table.   I have a quarterly budget table that shows Q1  Value   Q2  Value  etc   and a monthly budget table that shows the monthly budget for Jan, Feb, Mar, Apr, etc...

And for example, I have a visual that shows a % variance (measure) between  actual mtd and  and that month's budget.   But this is a specific measure for that specific month, in this case January.    So what happens when February 1 comes?   Somehow I need a measure for that same visual that automatically updates the formula  (actual/budget) for the new month.   Otherwise I have to manually update the formula from using January information to use February information.    Hopefully that makes sense.  

[amitchandak]

Ok. Do this in both budget tables. The month table have a month-end date. In Qtr table have Qtr end date.  Create Date calendar, if not done already; and join it with these tables. Now create a formula like this and use Qtr and month from Calendar table 

Period Wise Sales = if(ISFILTERED('Date'[Month Year]), CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date])),
		ISFILTERED('Date'[Qtr Year]) ,CALCULATE(SUM(Sales[Sales Amount]),DATESQTD('Date'[Date]))
		CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date])
		)
	)

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
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

in the pbix of this blog, I have share the period formula for month year, similar to above

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

Thanks for this - it looks promising!   One quick question.   I have everything you mention below (date table, etc) but not sure what you mean by "use Qtr and month from calendar table".    Use it where?  

[amitchandak]

Refer, How to create -https://softcrylic.com/blogs/power-bi-for-beginners-how-to-create-a-date-table-in-power-bi/

Whenever you have viewby/group by/row/col of Month or Qtr, Use from this calendar table.