sum filtering by shop name

[nurs]

Hi,

I have two tables, one with historic data from cases created by shop, and the other one with the names of the shop.

I want to create a new column on the second table with the sum of the cases opened on the last 12 months by each shop.

Can you help me plase?

Table one: cases created by shop

Shop	Case number	Created On
A	1	01/02/2023
B	2	04/02/2023
C	3	16/02/2023
A	4	18/02/2023

[amitchandak · Accepted solution]

@nurs , You should create a common Shop, date and case and join with both tables and create measure like

Rolling 12 = CALCULATE(count(Table1[casenumber]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH)) +

CALCULATE(count(History[casenumber]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...

Rolling Months Formula: https://youtu.be/GS5O4G81fww

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc