Forum Discussion

tonyclifton's avatar
tonyclifton
Helper 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

     

Daniel29195's avatar
Daniel29195
Community 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
Helper 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.

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    tonyclifton 

     

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

    • tonyclifton's avatar
      tonyclifton
      Helper III
      2 years ago

      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's avatar
        Daniel29195
        Community Champion
        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