DAX Measure to get aggregation of repeated values for the latest date

[dairec]

Hi there,

Id	Date	Country	Category	Gender	CurrentStock	UnitValue
1	1/1/2016	France	A	Male	6	1
1	1/1/2016	France	A	Female	6	2
1	1/1/2016	France	B	Male	6	5
1	1/1/2016	France	B	Female	6	7
2	1/1/2016	UK	A	Male	7	3
2	1/1/2016	UK	A	Female	7	4
2	1/1/2016	UK	B	Male	7	5
2	1/1/2016	UK	B	Female	7	8
21	1/2/2016	France	A	Male	3	10
21	1/2/2016	France	A	Female	3	5
21	1/2/2016	France	B	Male	3	4
21	1/2/2016	France	B	Female	3	3

Consider the dataset above which is the result of a data entry form with the Id column unique per form entry. On the form there are actually four Unit Value fields, one for each combination of Category and Gender “Category A Male Units” etc. However, there is only one CurrentStock field because this is not broken out by Category or Gender. The data from the form has been unpivoted into the table above for the model so we can have Category and Gender dimension fields for series and slicers etc. The Month and Country are single values per form.

The measure for UnitValue is a simple Total Units := SUM([UnitValue) since you can add these over all dimensions. However, for the CurrentStock I should only get one of the values per form Id but should be able to sum them over all forms. This seems to be working with:

Total Current Stock =

SUMX (

   VALUES ( 'Forms'[Id] ),

   CALCULATE ( MIN ( Forms '[CurrentStock] ) )

)

However, this is further complicated by the fact that CurrentStock is semi-additive i.e. is a rolling value. Forms are usually filled once a month (although you could have more than one in a month) with the CurrentStock representing the stock level at that time. So we should only get the most recent value for any given date range. But within that date range we should be able to see a total over all Countries. To achieve this I was looking to do something like the following:

Total Current Stock =

   CALCULATE (

       SUM ('Forms'[CurrentStock]),

       LASTNONBLANK (

           'Date'[Date],

           CALCULATE(SUM('Forms'[CurrentStock]))

       )

   )

But I need to combine these two ideas i.e. for the last non-blank date in the current date range, get the set of form data and then sum up the CurrentStock, taking only one value per form (Id). I think I should be using a GROUPBY with ADDCOLUMN but I got stuck at this point.

Any help would be appreciated.

Thanks,

Daire

[dkay84_PowerBI]

This is tricky to follow.  I think I understand what you want to be able to show/do, but without playing with it in Power BI it will be hard to solve.  Can you share a link to the PBIX or a data file that has expands upon what you show above?

[dairec]

Hi dkay,

thanks for asking.

I've uploaded a test PBIX here

Looking at the report, the Total Units is straight forward as it is just a sum across all dimensions.

Totak Stock shows how we should only get one CurrentStock value per form ID since stock is not actually broken down by Gender and Category. Total Current Stock shows how we should only get the most recent date's worth of values since stock is a rolling value. The problem is how to combine these two ideas.

Thanks in advance for any help,

Daire

[dkay84_PowerBI]

I think I have this solved...

First, I created a query referencing your "Forms" table named "FormsCurrentStock".  I used the query editor to group this new table by ID and Date, and the new column is the average (or min or max, probably doesn't matter in this example) of the CurrentStock field.

Then, after loading this to your data model, create the following measure:

Cumulative Stock = 
CALCULATE(
    SUM(FormsCurrentStock[CurrentStock]),
    FILTER(
        ALL('Date'[Date]),
        'Date'[Date] <= MAX('Date'[Date])
    )
)

Make sure that the relationship between this new table and your original forms table is active (using ID as the key).  Also note when you add "UnitValue" field (from the original "Forms" table) to the visual, please set the aggregation to "Sum".

I created a table visual and date slicer to test:

Please confirm if this is what you are looking for, as it was somewhat hard to follow.

[dairec]

Thanks for that DKay, appreciate the effort.

but I was hoping to acheive this in a DAX measure without modifying the query. In fact this is a mock-up and there is more stuff behind the real dataset and other columns beside.

Any idea how to achieve with a measure?

Thank,

Daire

[v-ljerr-msft]

Hi @dairec

---

but I was hoping to acheive this in a DAX measure without modifying the query. In fact this is a mock-up and there is more stuff behind the real dataset and other columns beside.

---

In this scenario, you can use SUMMARIZE Function (DAX) to create a calculate table like below first(without modifying the query).

FormsCurrentStock =
SUMMARIZE (
    Forms,
    Forms[Id],
    Forms[Date],
    "CurrentStock", MAX ( Forms[CurrentStock] )
)

Then you should be able to follow the other steps(except the create query part) mentioned by dkay84_PowerBI above to get your expected result.

Here is the modified sample pbix file for your reference.

Regards

[dairec]

THanks for that but it is the combining of the SUMMARIZE and the Max(Date)/LASTNONBLANK(Date) that I'm having the trouble with.

In the resulting calculated table I need to choose only the latest date for a given date range in this example I would have just a value of 7 for 1/1/2016. I have tried in the past to use the results of SUMMARIZE with something like:

Cumulative Stock =
CALCULATE([MaxCurrentStock], SUMMARIZE (
    Forms,
    Forms[Id],
    Forms[Date],
    "MaxCurrentStock", MAX ( Forms[CurrentStock] )
), FILTER(
        ALL('Date'[Date]),
        'Date'[Date] <= MAX('Date'[Date])
    )
)

But this gives an error for MaxCurrentStock because it is coming for a calculated table. I think I need to use ADDCOLUMN instead but that's the part I couldn't figure out.

Thanks and regards,

Daire

[dkay84_PowerBI]

I changed the calculated table DAX to this:

CurrentStock = SUMMARIZE(Forms,Forms[Date],"Current Stock", MAX(Forms[CurrentStock]))

which groups by date and not id, and thus shows a value of 7 for 1/1/2016.  Is that the correct value or shoud it be 13 (the combined value of stock for the two IDs from 1/1/2016)?  However, if the date range is set to the most recent date (2/1/2016), it gives a value of only 3.  So do you want the cumulative value?  In other words, a runing total?  That way when a user selects a single date, it gives them the total of stock as of that date for all IDs?

[dkay84_PowerBI]

After creating the summarized table 'CurrentStock', I created the following measure:

CumulativeQuantity = 
CALCULATE (
    SUM ( CurrentStock[CurrentStock] ),
    FILTER (
        ALL ( 'Date'[Date] ),
       'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

The result is that when 1/1/2016 (or any date up to 1/31) is selected, we get a value of 7.  When we select 2/1/2016 (or later) we get a value of 10 (7+3).  Not sure if this is what you need.

[dairec]

Hi DKay,

the currentstock column is already a cumultaive total so it should never be summed over time, we should get the latest version instead. So the result of the SUMMARIZE is correct where we get 7 for 1/1/2016 and 3 for 1/2/2016. But how to I turn this into a measure so that if for example I filter for 1/2/2016 I should show 3 only?

Thanks,
Daire

[dkay84_PowerBI]

You don't have to modify the source query.  You can duplicate it or reference it.  This is a much easier solution than a measure, IMO, and is likely to be higher performance

However, perhaps someone here can help find a DAX measure or calc column to do the same.  I will look into it

[GilbertQ]

Hi @dkay84_PowerBI, what about this DAX Calculation?

CALCULATE (

    CALCULATE (

        DISTINCTCOUNT ( 'Forms'[CurrentStock] ),

        ALLSELECTED ('Forms'[CurrentStock])

    ),

    ALLSELECTED ( 'Forms' )

)

Would that not work as expected?

[dairec]

Thanks guavaq, but that will give a value of 3 no matter what other columns i have in the report.

[ukeasyproj]

@dkay84_PowerBI

how did you get that date range slicer 

[dkay84_PowerBI]

Part of the November update (maybe October?).  Just a regular slicer, add date field, and you have option for traditional selection, drop down list, or range (including between, before and after, etc)

[dkay84_PowerBI]

If you want just the Current Stock level, regardless of ID, and you just want to see the most recent value depending on selected date, then I would think performing the original "Group By" operation with just Date (no ID) combined with a LASTNONBLANK style measure would give you that value.