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 

 

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

     

  • tonyclifton's avatar
    tonyclifton
    Helper III
    2 years ago

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

    tonyclifton 

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

     

    because the code works base on the dimdate . 

  • tonyclifton's avatar
    tonyclifton
    Helper III
    2 years ago

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

     

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    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 .