Can't display a slicer-filtered cumulated count in an area chart with an additional calendar table

[oduc78]

Hello,

I'll try to summarize my problem:

• I have a table named MyTable with 3 columns: PRODUCT (A, B, C, etc.), START_DATE, and VERSION (V1, V2, V3...):

• I would like to display in an date-based area chart the cumulated count of products produced over time AND taking into account the version selected in a slicer.

• Fot this I created a new Calendar table based on my MIN and MAX dates :

MyCalendar = CALENDAR(MIN(MyTable[START_DATE]), MAX(MyTable[START_DATE]))

and I created a 1<>Multiple relationship with the START_DATE column of MyTable:

I created a column to calculate the cumulated count that I'm looking for:

CumulNbProducts_Column = CALCULATE(COUNT(MyTable[PRODUCT]), FILTER(MyTable, MyTable[START_DATE]<=MyCalendar[Date]))

I also tried to create a measure to do the same kind of calculation but couldn't figure out how to make the filter on the date work.

• So when I try to plot my CumulNbProducts_Column versus the Calendar[Date] I get pretty much what I wanted with 1 point showing the cumulated count for each day of the whole period:

with 1 product on 1/01, 3 products on 1/02, 4 products on 1/04, etc.

• BUT if I select a particular version in a slicer, V2 for example, my chart displays only the 3 points corresponding to V2 but still counts the V1 and V3 products:

 

I get the same problem if I try to use the START_DATE as the time base for my chart.

Basically using the slicer filters out the dates but not the COUNT calculation. I can't figure out what I'm doing wrong. Either I should find a measure that will recalculate the count based on the version selected in the slicer or modify the relationship between the 2 tables.

Any idea?

Thanks in advance, regards.

Olivier.

[Anonymous]

Hi @oduc78 ,

Thanks for @FlipFlop1 and @HotChilli reply!

@oduc78 How is the situation now? If the problem has been solved, please accept answers you find helpful as solutions that will help others with the same problem.

Thank you to all for your contributions, which make the PowerBI community even more vibrant!

Best regards,

Mengmeng Li

[oduc78]

Hi Mengmeng, unfortunately I still haven't found a solution to my problem.

Olivier.

[oduc78]

Thanks to @HotChilli and @FlipFlop1 I can now count the cumulated number of products with a

MyTable[START_DATE]<=MyCalendar[Date]

AND filter with a slicer on the VERSION for example, thanks! 👍

My real table is actually a little more complex with a END_DATE in addition to the START_DATE:

so now I'm struggling to count the number of products with 2 dates conditions:

MyCalendar[Date] BETWEEN( MyTable[START_DATE]   AND   MyTable[END_DATE] )

I think that somehow the relationship between MyTable[START_DATE] and MyCalendar[Date] is blocking me from calculating on the MyTable[END_DATE] but I'm able to figure it out.

I've updated my COUNT calendar_LIGHT.pbix if anyone wants to take a look at it.

Thanks,

Olivier.

[FlipFlop1]

Create 2 Measures:

1. Product Cout = COUNTX('MyTable', MyTable[PRODUCT])

I used the quick measure to create the cumulative count (running total) as its complex:

2. Product Cout running total in Date =

CALCULATE(

    [Product Cout],

    FILTER(

        ALLSELECTED('MyCalendar'[Date]),

        ISONORAFTER('MyCalendar'[Date], MAX('MyCalendar'[Date]), DESC)

    )

)

Then drop the new running total measure into the graph:

You should always try to use measures instead of columns or calculated columns in your visuals.

[oduc78]

Hi @FlipFlop1 , thanks a lot for the code, it does work on my little example file! 😊 I had never seen the ISONORAFTER function so far.

I'm now trying to apply this to my real PBIX file with additional parameters. I still don't quite understand how START_DATE is taken into account in the calculation since it's never mentioned in the code of the 2 measures.

I will keep you updated when I'm done.

Olivier.

EDIT: of course START_DATE is taken into account through the relationship between the 2 tables! My bad!

[HotChilli]

It will have to be a measure (so that it responds to changes in the slicer).

You probably want the relationship direction between date and MyTable to be single direction - but that's not the main issue.

Re-write the DAX into a measure and change the FILTER clause so it only refers to the MyCalendar table-> so just a couple of small changes.

[oduc78]

Thanks for the tips @HotChilli .

I'll try to improve my measure. For now I tried this but it only counts the total for each existing day, not the cumulated count :

CumulNbProducts_Measure = CALCULATE(COUNT(MyTable[PRODUCT]), FILTER(MyTable, MyTable[START_DATE]<=SELECTEDVALUE(MyCalendar[Date])))

I'll work on it following your advice.

In the meantime here's an example file : COUNT calendar_LIGHT.pbix if anyone feels like taking a look! 🙂

Olivier.