Count Products over time dealing with two date fields

[tonyclifton]

Hello community,

I need to calculated the count of products over time (months for now, maybe with daily accuracy). I want to show the increase or decrease of products based on when they were "added" and/or "removed".

Example:

This is how I think the calculation should be:

Not sure about 2024-01: maybe a date selection is also required for a day comparision.
I created a calendar table for selection but I'm stuck with the calculation because two dates need to be dealt with (or DiscontinuedDate is NULL) and when I do something like below it always returns the same values for each month:
PARALLELPERIOD('Calendar'[Date],-1,MONTH))

I also have the same CreatedDates and DiscontinuedDates multiple times which throws an error (duplicate dates) when I use PARALLELPERIOD.

Any tips on how this can be achived are highly appreciated.

Thanks in advance.

[Ashish_Mathur]

Hi,

Follow the instructions mentioned here - Generating Rows by Month for Date Ranges in Power Query | by Daniel Marsh-Patrick | Daniel Marsh-Pat...

Hope this helps.

[Daniel29195]

JHello @tonyclifton ,

if you could provide some sample data, that would be helpful.

How to provide sample data in the Power BI Forum
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

How to Get Your Question Answered Quickly
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[tonyclifton]

Hello @Daniel29195 

The table below pretty much sums up my problem because I don't see how to dynamically calculate the valid date range and then show the differences over the months.

Example data:

Product	CreatedDate	DiscontinuedDate
A	01.01.2023
B	01.01.2024	31.01.2024
C	03.01.2024	30.06.2024
D	04.01.2024	31.12.2024

The problem I see is that I would need to calculate a valid date range for both dates so that I can show the changes over time.
With the link provided by @Ashish_Mathur I might create other problems by duplicating rows in the source. I will have a closer look though - it might work by using it as a lookup table or so.

Isn't there a way with DAX to dynamically calculate the valid date range?

Thanks alot.

[Daniel29195]

@tonyclifton 

base on this sample data : 

this is the dax code in case you want to expand your data and get the number of active products per day :  ( NB if enddate is blank i took the date to be 2024-12-31 , but it can be changed to take any dynamic value ) 

tttr = 

var calendar_per_product = 
FILTER(
GENERATE(
    VALUES(exp_table[product]),
    var pr = exp_table[product]
    var createddate = SELECTCOLUMNS(FILTER(exp_table,exp_table[product] = pr),exp_table[createddate])
    var enddate = SELECTCOLUMNS(FILTER(exp_table,exp_table[product] = pr),exp_table[enddate])
    return
    CALENDAR(
        if(
            ISBLANK(createddate)  , DATEVALUE("2024-01-01"),createddate)
        ,
        if(
            ISBLANK(enddate)  , DATEVALUE("2024-12-31"),enddate)
        )
    ),
    NOT ISBLANK(exp_table[product])
)

var gds = 
GROUPBY(
    calendar_per_product,
    [Date],
    "c", countx(CURRENTGROUP() , DISTINCTCOUNT(exp_table[product]))
)

    return gds

output table : 

let me know if this can help.

[Daniel29195]

@tonyclifton 

if you dont want to create a table : 

this is the measure you can use : 

sample data : 

output :  as you can see, base on the sample data, you have in 2024-01-03 , 3 products active, which is displayed in the visual.

measure : 

count of active products per day = 

var calendar_per_product = 
FILTER(
GENERATE(
    all(exp_table[product]),
    var pr = exp_table[product]
    var createddate = SELECTCOLUMNS(FILTER(exp_table,exp_table[product] = pr),exp_table[createddate])
    var enddate = SELECTCOLUMNS(FILTER(exp_table,exp_table[product] = pr),exp_table[enddate])
    return
    CALENDAR(
        if(
            ISBLANK(createddate)  , DATEVALUE("2024-01-01"),createddate)
        ,
        if(
            ISBLANK(enddate)  , DATEVALUE("2024-12-31"),enddate)
        )
    ),
    NOT ISBLANK(exp_table[product])
)

var gds = 
GROUPBY(
    calendar_per_product,
    [Date],
    "c", countx(CURRENTGROUP() , DISTINCTCOUNT(exp_table[product]))
)

var res=
MAXX(
    FILTER(gds,[Date] in  VALUES(dimdate[Date])),
    [c])
    
    return res

NB: dimdate should not be linked to the table dim_products. 

and the visual is reading date from dimdate. 

NB: the measure may need some minor tweakings that i didnt do, just to know if this would be helpful to you . 

[tonyclifton]

@Daniel29195thanks alot for your effort. I just tried to replicate your example with the same sample data but I get an error:

What am I missing?
Thank you.

[Daniel29195]

@tonyclifton 

im not sure.  do you think it is possible to share the file so i can take a look ? 

[tonyclifton]

I use below code for the table creation and call the table "exp_table" just like in your example.
For the measure I just pasted your code from above.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AMiIwMjYyBHQSlWJ1rJCVnYBMgxRnBA8s4geWNkeQM9AzOEvAtI3gRVv6ERVD4WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, createddate = _t, enddate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"createddate", type datetime}, {"enddate", type datetime}})
in
    #"Changed Type"

[Daniel29195 · Accepted solution]

@tonyclifton 

tried it .

did work from my side . ( change my table to query2 , since i already have a table called exp_table)

ok i think i know the problem you are encountering . 

change the code to this : 

count of active products per day new = 

var calendar_per_product = 
FILTER(
GENERATE(
    all(Query2[product]),
    var pr = Query2[product]
    var createddate = SELECTCOLUMNS(FILTER(Query2,Query2[product] = pr),"createddate",Query2[createddate])
    var enddate = SELECTCOLUMNS(FILTER(Query2,Query2[product] = pr),"enddate",Query2[enddate])
    return
    CALENDAR(
        if(
            ISBLANK(createddate)  , DATEVALUE("2024-01-01"),createddate)
        ,
        if(
            ISBLANK(enddate)  , DATEVALUE("2024-12-31"),enddate)
        )
    ),
    NOT ISBLANK(Query2[product])
)

var gds = 
GROUPBY(
    calendar_per_product,
    [Date],
    "c", countx(CURRENTGROUP() , DISTINCTCOUNT(Query2[product]))
)

var res=
MAXX(
    FILTER(gds,[Date] in  VALUES(dimdate[Date])),
    [c])
    
    return res

[tonyclifton]

Thank you very much. It works now with the sample data.
Before I'll try to put this in my real data report can you tell me how to deal with adding slicers that affect the calculation?
For example, with the sample data, if I use CreatedDate as a slicer or have another column like "plant" it will always output a different calculation for each month - compared to not being filtered at all.

Any ideas how to deal with that?
Thank you.

[Daniel29195]

@tonyclifton 

for date, you should filter from dimdate  and not createddate . 

because the code works base on the dimdate . 

[tonyclifton]

I agree with dimdate but what about filtering by other columns of the original table like Plant for example?

[Daniel29195]

@tonyclifton 

i cant tell, since i dont have an idea how the model is structured. 

but i assume it should work, if you have plant column that already filters the data .