Forum Discussion
sum filtering by shop name
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 |
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-solution-8e3eccb41bdaRolling 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
1 Reply
- amitchandak
Super User
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-solution-8e3eccb41bdaRolling 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