Calculating ITD?

[agent]

Hey everyone,

I'm having an issue calculating the ITD value of my dataset. It seems like it should be pretty simple but I can't get it to work properly. Here's what my sample data looks like:

Transaction Date	Category	Amount
1/15/2017	Purchase	1,000
1/28/2017	Distribution	50
2/20/2017	Distribution	20
3/9/2017	Purchase	200
3/26/2017	Purchase	300
6/30/2017	Purchase	150
8/25/2017	Distribution	30
10/10/2017	Distribution	40
10/21/2017	Purchase	400
11/30/2017	Purchase	500
12/22/2017	Distribution	70
1/5/2018	Purchase	200
1/17/2018	Distribution	50
2/23/2018	Purchase	200
3/21/2018	Distribution	40

I also have a "categories" table:

Name

Purchase

Distribution

And I've created a date table with the following code:

Date Table = CALENDAR(DATE(2017, 1, 1), DATE(2018, 12, 31))

I've marked this table as the date table in Power BI Desktop. All the basic relationships between the tables have been set up.

Here's what I came up with to try to calculate the ITD value:

ITD = 
VAR CurrentMonth = MAX('Date Table'[Date])
VAR CurrentCategory = VALUES('Categories'[Name])
RETURN
    CALCULATE(
        SUM([Amount]),
        'Data'[Transaction Date] <= CurrentMonth,
        'Data'[Category] IN CurrentCategory
    )

When I plug that measure into a column chart with no "legend" breakdown, it looks fine:

When I try to add a breakdown by category, it doesn't work as I expected:

It is calculating the ITD for each category but in months where no transaction of that specific category type occurred, it's not showing a bar. I'd like it to always show a bar even if there was no transaction of that category type in that month - it would just be the same as the last month's bar.

Any ideas? I've been banging my head on this for a while now!

Thanks!

[MFelix]

Hi @agent,

When adding the filter CurrentCategory to your measure you are forcing the calculation to cgeck if the category exists on your table if not it won't return value try to remove the last part of the filter from your calculation being in the measure only the date filter.

Regards,
MFelix

[agent]

Thanks @MFelix. That doesn't quite work either. Here's how it looks if I take out the CurrentCategory filter:

 

[MFelix]

Hi @agent,

Try to use the TOTALYTD formula:

Total = TOTALYTD (SUM(Data[Amount]); Datetable[Date])

Regards
MFelix

[agent]

Thanks @MFelix. It still doesn't work once the category breakdown is added. Here's what the TOTALYTD looks like without the category breakdown:

This looks fine - as expected. Once I add the category breakdown, it turns into this:

Any other ideas?

[agent · Accepted solution]

Never mind, got it figured out. As counterintuitive as it would seem, I had to remove the relationship between the Data table and the Categories table.

Hopefully this helps someone else in the same boat.

[MFelix]

To make your legend you used the category from data table or category table?

[agent]

The Category table. I guess the fact that there's no relationship between the two tables forces Power BI to calculate the measure on each of the categories.

[MFelix]

Hi @agent,

Using your data and you setup and having the active relationship between data and category I got the correct result. Check the PBIX attach and see if you have something that isn't machthing in your model, maybe the way the relationship is setup.

I also added a table with the the amounts and YTD calculations just to be sure everything was ok.

Regards,

MFelix