Combining two measures based on different dates to the same visual

[GunnerJ]

I want to show two seperate measures next to each other on a bar chart. However the filter context seems to mess up when I put them on the same chart. I tried creating a date table but haven't had luck with that. My end goal is to show "yearly Disconnects" and "yearly active subs" side by side. They show the total active subscribers and the total # of lost accounts during a calendar year.

In the screenshot below you can see how the (top) disconnects don't line up like the do in the middle visual. 

Yearly Disconnects =

VAR FirstDayYear = CALCULATE(STARTOFYEAR('Attrition Rate'[MAXDISCDT]))

VAR LastDayYear = CALCULATE(ENDOFYEAR('Attrition Rate'[MAXDISCDT]))

VAR yearlydisconnects =

CALCULATE(

COUNT('Attrition Rate'[ACCT]),

FILTER(

ALLSELECTED('Attrition Rate'),

AND('Attrition Rate'[MAXDISCDT] <= LastDayYear,'Attrition Rate'[MAXDISCDT]>= FirstDayYear)

),'Attrition Rate'[MINDISCDT] > DATEVALUE("1/1/1800"), USERELATIONSHIP('Attrition Rate'[MAXDISCDT],'Attrition Date'[Date])

)

Return yearlydisconnects

--------------------------------------------------------------------------------

Yearly Active Subs =

VAR FirstDayYear = CALCULATE(STARTOFYEAR('Attrition Rate'[STARTDT]))

VAR LastDayYear = CALCULATE(ENDOFYEAR('Attrition Rate'[STARTDT]))

VAR yearlysubs =

CALCULATE(

COUNT('Attrition Rate'[ACCT]),

FILTER(

ALLSELECTED('Attrition Rate'),

AND('Attrition Rate'[STARTDT] <= LastDayYear, 'Attrition Rate'[STARTDT] <> BLANK())

),'Attrition Rate'[MINDISCDT] = DATEVALUE("1/1/1800"),'Attrition Date'[Date]<>BLANK()

)

Return yearlysubs

This link has the test file in question if it will help. Please let me know if there's any additional info I could provide. 

https://www.dropbox.com/s/58jc5ksiacerdpz/Test%20Attrition%20File.pbix?dl=0

[V-lianl-msft · Accepted solution]

Hi @GunnerJ ,

I checked your file. The problem is that the context of the filter conditions of the two measures is inconsistent.

Please use the calendar table as the x-axis and the calendar table as the filter table. Please refer to this pbix.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CheenuSing]

Hi @GunnerJ ,

Can you explain what exactly you want to compute and what should be expected values to be shown as output.

Cheers

CheenuSing

[GunnerJ]

@CheenuSing Yes, I want to see teh Monthly Disconnects and Monthly actives on the same bar chart. Examples for 2017 are shown below. For disconnects I have the accounts shown to illustrate how the monthly value is determined. Jan, Mar, and May all have one account each so their total is one each. June and July had 6 accounts so their monthly totals are 6. It's the same concept for active subs but just with it's own measure. "Monthly Disconnects2" and "Monthly Active Subs" but give the correct values when compared to the date value shown in their measure but I need both measures on the same visual for easy comparison. Does this help?

[GunnerJ]

@CheenuSing was my last reply of any help? Please let me know if any addtional information would be useful!