Forum Discussion

tonyclifton's avatar
tonyclifton
Icon for Helper III rankHelper III
2 years ago
Solved

Count Products over time dealing with two date fields

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.


 

Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    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

     

13 Replies

    • tonyclifton's avatar
      tonyclifton
      Icon for Helper III rankHelper III
      2 years ago

      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:

      ProductCreatedDateDiscontinuedDate
      A01.01.2023 
      B01.01.202431.01.2024
      C03.01.202430.06.2024
      D04.01.202431.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's avatar
        Daniel29195
        Icon for Community Champion rankCommunity Champion
        2 years ago

        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.