How to measure diff between previous month and this month?f For use in line / column chart

[Jackofall]

Part of the problem is that I use a Measure as Column value over the axis Period. I use a relative filter of the last 3 montshs.

Measure = DISTINCTCOUNT('Membership'[MembershipId])

The chart looks good enough.

So far I failed to get my differnces right. 49845 - 49607 and 49607 - 49483. I tried;

diff = sum('Table'[NoMemb])-CALCULATE(SUM('Table'[NoMemb]);PARALLELPERIOD(Date[Date];-1;month ))

NoMemb column contains the value 1 for counting instead of the measure.

My second try was...

diff2 =

;PREVIOUSMONTH(Date[Date]))

I fail epicly the figures are nowhere close to what I want.

[v-chuncz-msft]

@Jackofall,

You may add an additional filter in CALCULATE.

ALLSELECTED ( 'Table' )

[Jackofall]

I am not sure how to solve the problem with Allselected?

[v-chuncz-msft]

@Jackofall,

Just add two filters in CALCULATE.

[Jackofall]

I will go back and experiment with this. I got so irritated that I suggested that we make a separate table in the DB. What our customers think about is the number of x, y, z this month, previous month and any old comparison. I might as well make a corresponding table that keep track of this month and the figures up to 12 months back in time.

Still I will fool around with the filters for the principle of it.

[Jackofall]

OK, I think I am to stupid to grasp DAX. I simply can't stop thinking C# / T-SQL.

My measure No of members DISTINCTCOUNT(Membership[MemberIshipd])

My columns grouping is a string(PeriodMonth) to force my columns as I want.

Then I assume to calculate the difference between  201812(66509) - 201901(66752).
I use two tables Membership and Date. They are joined on PeriodMonth(string).

Then I experimented creating two measures;

201812 = calculate(DISTINCTCOUNT(Membership[MembershipId]);Membership[PeriodMonth]="201812")

201901 = calculate(DISTINCTCOUNT(Membership[MembershipId]);Membership[PeriodMonth]="201901")

The result in a Table really confused me. I would have expected the values to be 3 rows of (66752 | 66509)

Then I filtered on PeriodMonth in the Date-Table.

201901 = calculate(DISTINCTCOUNT(Membership[MembershipId]);'Date'[PeriodMonth]="201901")

The reuslt of that behaved like I had hoped from the beginning.

I just don't get how filtering on the same key in different tables get different results in my Tile. DAX gives me frustration on a whole new level.

In T-SQL or Excel I could have solved my problem within minutes.

[AlB]

Hi @Jackofall

Why are you using DISTINCTCOUNT('Membership'[MembershipId]) first and then SUM('Table'[NoMemb])?

Can you share the pbix? Usually with time intelligence functions it's difficult to see what the issue is without the model    

[Jackofall]

I did it because the first column is a measure and will not show up in sum('Table'[Col]). Since I work towards some databases I will be back ASAP a proper PBIX in this thread.

[Jackofall]

I created a sample of the pbix with vetted data but I cant add a file.