Help with cumulative chart - don't understand how ALLSELECTED and ISONORAFTER makes this work

[chraaroe]

I've thrown myself into the deep end and have started using PowerBI to do internal reporting on the usage of our in-house developed software which is hosted Azure.

I've gotten a great start on building a basic datamodel and I'm up and running with some simple metrics. It's been easy and fun... so far 🙂

I've gotten requests for a few more metrics and one of them in particular is kicking my butt to the point where I need to ask for a bit of guidance to make things click.

The basic idea is, that I want to measure an activity level by measuring how many messages are being sent to our system over time. This should be plottet as a cumulative chart - one for the current year and one for last year. I had no idea how to do this, but I found that by doing a "New quick measure" in PowerBI it seemed to give me a helping hand. I was able to accomplish what I wanted letting the wizard guide me through creating a "running total". I ended up with a new measure which, when I plotted it in a line chart looked exactly as I wanted:

With that in place, I now had the cumulative chart for this year - and I expected to be able to just edit the DAX expression for the Measure to easily create one which reported on 2019. I was wrong! The DAX expression which PowerBI created looks like this:

Count of ResultID running total in IndexDate = 
CALCULATE(
	COUNTA('Results'[ResultID]),
	FILTER(
		ALLSELECTED('Results'[IndexDate]),
		ISONORAFTER('Results'[IndexDate], MAX('Results'[IndexDate]), DESC)
	)
)

And so far, I have not managed to get anywhere close to a solution on how to fix this expression to report only on 2019. I've figured out, that the cumulative part seems to be heavily controlled by the use of both ALLSELECTED and ISONOROAFTER since I'm not able to change any of them without everything falling apart.

I hope you're able to provide me a bit of guidance. I'm stuck.

Thanks in advance.

[amitchandak]

@chraaroe , If you need this vs last year cumulative, then use YTD with a date table

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

If take two years in visual , YTD will reset in Jan

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
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi



Appreciate your Kudos.

[chraaroe]

Hi @amitchandak

I have Calendar table hooked up. I'm not sure what you mean about "marked as the date in model view". But I have marked it as a the "date table" in the Data view. I think I'm OK set up.

I've tried to interpret your suggestion for a Last YD Sales and has come up with the following:

CALCULATE(
    COUNTA('Results'[ResultID]),
    DATESYTD(DATEADD('Calendar'[Date], -1, YEAR), "12/31")
)

It should be identical to what you've suggested. However, the resulting chart looks the same as all my own attempts up until this point:

I don't want all the fluctuations. I want the continuously rising rising curve but it keeps escaping me. I'm not sure what is causing it and how to get a curve similar to the one in my original post.

[amitchandak]

@chraaroe , this does not look like a YTD visual. I Hope X-axis is coming from Calendar Table.

Can you share a  sample pbix after removing sensitive data.

[chraaroe]

Hi @amitchandak , Thank you for the tip regarding making sure the x-axis is from the Calendar Table. I'm not quite sure why that makes a difference but that's at least a concrete thing I can look into 🙂

Changing the x-axis to the Calendar Table ended up resulting in this chart:

This looks a lot more like it. If I may ask you one more question, there's a thing which puzzles me: I thought the chart would only be showing the count for last year. If there's no filters applied on the chart, it shows all of the years of data. Why is that?

If there's an error in my PBIX (which there most likely is 😄 ) I'll try to clean it from sensitive data and link it from here.

Thank you for your time, @amitchandak 

[Anonymous]

"I'm not quite sure why that makes a difference"

It does make a difference because of how filtering works. If you have a measure that should work with a Calendar, then you should use fields from the Calendar only. By the way, you should never expose table fields which are ID's and foreign keys. They should never be filtered by. And if you have a Calendar table, you should use it and only it to filter by dates.

"If there's no filters applied on the chart, it shows all of the years of data."

If there are no filters on the Calendar table, all dates are visible to DAX and hence you see all of them in the chart.

[chraaroe]

Hi @Anonymous , thank you for chiming in.

While I think it makes sense what you're saying, I can't really wrap my head around it in my PBIX file.

You see, what's driving my effort right now is to make this KPI visual work - and I want to be compare the number of submitted reports YTD with the same time period last year. And have that. With @amitchandak I have this here working:

Which, from the look of it, looks OK. At least it's a cumulative curve right now :). However, to get this, to work, the visual is currently filtering in to show only 2019 and 2020 from the [Year] column which is on my Calendar table.

The issue right now is that the 1.486 isn't correct. That figure is the combined total of the collected reports in both 2019 and 2020.

I'm not sure I can make things 'click' in my mind with what you said, when I'm supposed to be able to provide this visual two sets of data from the same table but sliced differently based on date.

Thanks again for all the help.