Dynamic DAX Measure

[Solvera]

I have a scenario where I need to check per month in three month intervals if a prodcut was sold, if it was I then need to sum the occurrences and not the units that were sold. Let me illustrate below:

First I need to check in three month intervals for each month if the product was sold:

If it was, I then need to sum the occurences:

I have a Date Table I use to change the reporting window, so this needs to be a dynamic calculation which I assume DAX is best at.

I have gone as far as checking units sold in the last three months with the below DAX measure but not able to sum the occurrences:

3M Check =

CALCULATE(SUM(Sales[Units]),DATESINPERIOD(DateTable[Date],MAX(Sales[Date]),-3,MONTH))

Any help will be appreciated.

[dufoq3]

What would be expected result for thois new task?

[omjcontabil]

In fact the Total still strange, and the resulto of new task below is more insightfull, I think.

[dufoq3]

It is possible to do this in PowerQuery, but why would we do that? You can use pivot table in excel or some basic visual in power bi for this. Do you have the totals correct? If yes could you explain?

[omjcontabil]

We don't have a pivot table in Excel, we are talking about a formula that takes the values ​​(or quantities) of the last 3 months, and in this case these last 3 months would still be products, I believe that in Power Query it would be simpler than in Power BI, since I understand that Power BI has to consider context and other things, I like Power Query better because it's much simpler. In Excel, yes, it could be done as long as your database does not have more than 300,000 rows, which brings us back to Power Query.

[dufoq3]

Hi @Solvera, because you asked in PowerQuery forum I'm providing PowerQuery solution:

Before

After

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bU9BDsAgCPuLZw+CiPoWsz/s/6chKDPLEoJICy1jhBtCDEAk2aOipM6SMocrCglP+A3FslTYmrW8oIwnae4HxUyLCnuNwD7Ql2Cx4Y+x1VEG/1pCSgbXfcfsyPa0fbX5LXVLKll9YTquP15VvB4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Dec-23" = _t, #"Jan-24" = _t, #"Feb-24" = _t, #"Mar-24" = _t, #"Apr-24" = _t, #"May-24" = _t, #"Jun-24" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Dec-23", Int64.Type}, {"Jan-24", Int64.Type}, {"Feb-24", Int64.Type}, {"Mar-24", Int64.Type}, {"Apr-24", Int64.Type}, {"May-24", Int64.Type}, {"Jun-24", Int64.Type}}),
    ColNames = List.Skip(Table.ColumnNames(ChangedType)),
    Ad_SoldProducts = Table.AddColumn(ChangedType, "SoldProducts", each
        [ a = Record.SelectFields(_, ColNames),
          b = Record.ToList(a),
          c = List.Transform({0..List.Count(b)-1}, (x)=> List.Sum({ try b{x-2} otherwise null, try b{x-1} otherwise null, b{x}? })),
          d = List.Transform(c, (x)=> if x = null then 0 else 1)
        ][d], type list),
    Zipped = List.Zip(Ad_SoldProducts[SoldProducts]),
    Summed = List.Transform(Zipped, List.Sum),
    SoldProducts = List.Accumulate(
        List.Zip({ ColNames, Summed }),
        #table(type table[Sold Products=text], {{"Total"}}),
        (s,c)=> Table.AddColumn(s, c{0}, each c{1}, Int64.Type) )
in
    SoldProducts

[omjcontabil]

Excellent tip, but what would it be like if instead of counting the products they were the sum of them and listing not just the total but the products themselves, I tried to change it, but I couldn't change List.Accumulate, it always gives an error.