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 "adde...
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

     

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.

     

     

     

    • Daniel29195's avatar
      Daniel29195
      Icon for Community Champion rankCommunity Champion
      2 years ago

      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's avatar
        tonyclifton
        Icon for Helper III rankHelper III
        2 years ago

        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.