Using a date table to dynamically calculate a measure

[remingtonh]

Hi all!

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.

[amitchandak]

Check out this example where I have put a filter on the Last Qtr as well as on this QTR. Also, you might have put crossfilter.

Sales Before QTR = 
CALCULATE(SUMx(FILTER(sales,and(Sales[Order_Date]>= STARTOFQUARTER(DATEADD(STARTOFQUARTER(OrderTime[Order Date]),-1,DAY)) && Sales[Order_Date]<= DATEADD(STARTOFQUARTER(OrderTime[Order Date]),-1,DAY),
Sales[Requested_Date]>= STARTOFQUARTER(OrderTime[Order Date]) && Sales[Requested_Date]<= STARTOFQUARTER(OrderTime[Order Date])
)),Sales[Sales]),CROSSFILTER(Sales[Order_Date],OrderTime[Order Date],None))