Using a Date Table to dynamically calculate a measure in PowerBI

Question

I'm attempting to use a date table,called date, formatted as so

Date         Month   Day   Year
01/01/2019   01      01    2019
01/02/2019   01      02    2019
....

with another table,called A, formatted as so

ID    DateStarted   Type
01    12/30/2018    apple
02    12/30/2017    apple
03    12/31/2018    carrot
...

I'd like to create a measure or something, so I can show in a graph dynamically the # of apples/carrots where the start date is within 12 months of some random date in the Date table (I'd like to show the Date tables ['Date'] field as my axis on a graph, with the calculated "within 12 months" as my value for instance, and then be able to use the "type" field as my legend value. So for the above example, I would see Jan-Dec of 2019 on the graph, and then see a value of 1 and 1 in Jan of 2019 for apples and carrots (as the other apple was shipped 24 months prior to that.) Then, in Feb of 2019 on the graph, both values would be 0.

I've attempted messing around with Countax, but I can't seem to get it to work, as there are multiple values in the Date table, and powerbi would like me to aggregate them.

Shipments in past 12 months = COUNTAX(MDBEQUIP,(DATEDIFF(Dates[Date],MDBEQUIP[Tag added date],MONTH)<12))

Is this the best approach? Another idea I had was trying to dynamically create a new table with all calculated columns with something like

Month    Type    Calculated column for above

But wasn't sure if there was another way.

Can you show the expected result of your idea in paint? because I think you might be able to do it. Create a bar chart with Years and Months on axis, distinct count of id product and product name in legend. Then you can drill down or expand years and check those values — ibarrau

greggyb greggyb · Accepted Answer · 2019-09-10T17:27:36

A Count = COUNTROWS ( 'A' )

A Count Rolling 12 months =
VAR CurrentEndOfMonth = EOMONTH ( MAX ( 'Date'[Date] ), 0 )
RETURN
  CALCULATE (
    [A Count],
    DATESINPERIOD ( 'Date'[Date], CurrentEndOfMonth, -11, MONTH )
  )

Assuming a relationship 'Date' -1:N-> 'A'.

Also, I'm not entirely sure on what your intended output is. December, 2018 is 2 months before February, 2019. Thus, I would expect to see a count of 1 for each of "apple" and "carrot" based on a rolling 12 month logic. So if the measures above aren't what you're expecting, you'll need to clarify more on what you're trying to achieve.

Using a Date Table to dynamically calculate a measure in PowerBI

1 Answers