Calculated table with variable time frame

IsseBisse — Tue, 17 Jan 2023 11:08:57 GMT

I have a bunch of transaction data like this:

Date	Customer	Amount
2022-03-16	Customer A	1
2022-04-16	Customer B	2
2022-03-18	Customer A	3
...	...	...

and a table with expected sales (and more metrics) per period like this:

Customer	Period	Expected sales per period
Customer A	Month	10
Customer B	Quarter	20
Customer C	Year	30
Customer D
...	...	...

I want to create a report view with total sales compared to expected sales, where you can select either the current period or the previous period. How should I go about doing this? I want something like this:

User selects current or previous period (with checkbox-like slicer)
Based on current date and customer's "calculation period" calculate the correct dates (e.g. 2023-01-01 to 2023-01-31 if date is today and period is month)
Filter transaction data based on calculated period and calculate a bunch of metrics.

But I can't figure out how to implement it!

topic Calculated table with variable time frame in DAX Commands and Tips

Calculated table with variable time frame