MTD, QTD, YTD Variance card visuals, MTD is accurate QTD and YTD are not HELP

[rgreener]

Hello-

I have been emersed in Power Bi for a few months now and an certainly still new at this. I have a table visual where each column is represented by a measure. I am working with multiple tables: Budget, DateKey, Road Service Activity and Road Service Clearing Codes; all with many to one relationships with cross filter direction set to both except the Budget Table is single direction one to many connected to the Date table. The two road service tables house all data elements related to each road service event. Each event is designated by its own row  so I am working with almost a million rows in those tables ( hundreds of events each day). My budget table has a monthly cost and call volume figure which I have tried to allocate over each day each month. The amounts vary month to month. I have turned off the total row and am trying to use card visuals under some of the important columns to show MTD, QTD And YTD figures. As you can see I have not added in QTD yet. The MTD cards seems to be accurate for all cards. When switching to the QTD and YTD cards only some of the cards are accurate. The cards that are accurate are not varience cards, they have formulas like this:

 

Total Vol MTD = TOTALMTD([Total Calls],DateKey[Date])

Total Vol YTD = TOTALYTD([Total Calls], DateKey[Date])

CC YTD = TOTALYTD([Cont Cost],DateKey[Date])

Fleet AR% MTD = TOTALMTD([Fleet AR%],DateKey[Date])

Fleet AR% YTD = TOTALYTD([Fleet AR%],DateKey[Date])

Total Calls = CALCULATE(COUNTROWS(View_ROAD_SERVICE_COMBINED),View_ROAD_SERVICE_COMBINED[CALL_NO])

Cont Cost = SUM('RS_RS History_COST'[Total Cont Cost])

Fleet AR% = DIVIDE(View_ROAD_SERVICE_COMBINED[# of Fleet Calls],View_ROAD_SERVICE_COMBINED[Total Calls])

Total Cont Cost = calculated field

# of fleet calls = calc field

Total Calls = Calc field

All of the above seem to work great. My issue is with my varience cards. Obviously I am not understanding the methodology here well enoough and would be most appreicative if someone would take a look at this for me with more expernice than I have.

These are my MTD measures that seem to be working, all other QTD and YTD are not accurate.

Call VAR % MTD = TOTALMTD([Call Var %],DateKey[Date])

Cost VAR MTD = TOTALMTD([FORMAT(VAR)],DateKey[Date])

VAR = IF(HASONEFILTER(DateKey[Date]),

[Cont Cost]- [Budget Daily Allocation 3],

[FORMAT(VARMTD)])

( I used the has filter to make the total on the table visual accurate but decided to turn totals off.

 

Budget Daily Allocation 3 =

VAR MonthDays = DAY(EOMONTH(MIN(DateKey[Date]),0))

VAR DailyBudget = CALCULATE([MTD Cost Budget], ALL(DateKey), VALUES(DateKey[Month & Year]))

RETURN

IF(ISFILTERED(DateKey[Date]),

DIVIDE(DailyBudget,MonthDays,0))

( I use this measure to calculate the daily cost budget from the monthly budget number)

How should I go about getting these YTD and QTD visuals to work no matter the selected year. Thank you so much in advance. Dont hold back, if I am way off base pleae slap me around!

Thank you

YTD DAX measure 

YTD, QTD, MTD 

Re: Actual & Budget DAX measure 

[Anonymous]

If these two measures do not work correctly, then it means something is wrong with your model.

[Total Vol YTD] =
calculate(
	[Total Vol], -- or any measure
	datesytd( DateKey[Date] )
)

[Total Vol QTD] =
calculate(
	[Total Vol], -- or any measure
	datesqtd( DateKey[Date] )
)

By the way... Bi-directional filtering should be enabled between tables only when the consequences of doing so are fully understood by the modeler. This kind of filtering is dangerous and can produce incorrect numbers when least expected and the modeler will not be even aware of this. So, please learn all there is to learn about this way of filtering and when it's needed. 95% of the time only one-way filtering from dimension to the fact table is needed and correct.

Best

D

[rgreener]

Hello and thank you for your response. I miss spoke. The relationships are all one to one, not one to many. I will change the filter direction to single based on your input.  I believe that Power BI automatically selected that filter direction and relationship for me, I will adjust this. I am sure it is possble that the model has an issue as you pointed out.  I will test those measures again and if there is an issue I will rebuild. 

Much thanks.

[Anonymous]

Wait... How is it possible that the relationships are one to one? No sane model would be built this way.

A correct model is a star schema where dimensions are connected to the fact tables in a one-to-many manner... You must have made a mistake.

Best
D

[rgreener]

I will be doing much reading on this to get smarter. THank you for pointing this out.

[rgreener]

Hello-

Thank you for your reply again and patience here. I understand star and snowflake schema, certianly not as much as you do I imagine. I have one main fact table with four dimention tables. All relationships are many to one. I just recently brought another dimention in and it looks like the relationship is 1:1 which as you stated would be an issue. I am going to change that relationship and see if I have any better luck. Thanks again for sharing your knowledge.

Ryan