Forum Discussion
Create a table visual to show calculations for different time periods
I would like to create a table visualization that would show total sales for different time periods, to look like the screenshot below, but have the totals calculate correctly for each period.
I have created a manual table 'Periods to Show' (screenshot below) to contain the time periods I want to show and units I think will be needed for the calculation.
I have the following metric, and want to somehow leverage the 'Number' and 'Unit' fields from the table above to feed into the DATESINPERIOD function (parts highlighted in red). 'Dates' is my calendar table, and 'Sales Data'[Start Date] is the last sale date - to be used as the starting point for the period calculations.
Is it possible to swap out the highlighted parts with references to the 'Periods to Show' table to get the totals I want, or is this wishful thinking? Or is there a better way to accomplish what I am trying to do?
StevenHiatt , Try like, I have not tested it can take period as variable.
A measure like
Period Sales =
CALCULATE(
SUM('Sales Data'[Total Sales]),
DATESINPERIOD(Dates[Date], Max('Sales Data'[Start Date]), - 1*max(Table[Number]), Max(Table[Unit]))
)If it give error for period then write like
Period Sales = Switch (True(),
Max(Table[Unit]) = "Month",
CALCULATE(
SUM('Sales Data'[Total Sales]),
DATESINPERIOD(Dates[Date], Max('Sales Data'[Start Date]), - 1*max(Table[Number]),Month )
),Max(Table[Unit]) = "Year",CALCULATE(
SUM('Sales Data'[Total Sales]),
DATESINPERIOD(Dates[Date], Max('Sales Data'[Start Date]), - 1*max(Table[Number]),Year)
),Max(Table[Unit]) = "Year",CALCULATE(
SUM('Sales Data'[Total Sales]),
DATESINPERIOD(Dates[Date], Max('Sales Data'[Start Date]), - 1*max(Table[Number]),DAY)
))
Table is having those 7 days etc
2 Replies
- amitchandak
Super User
StevenHiatt , Try like, I have not tested it can take period as variable.
A measure like
Period Sales =
CALCULATE(
SUM('Sales Data'[Total Sales]),
DATESINPERIOD(Dates[Date], Max('Sales Data'[Start Date]), - 1*max(Table[Number]), Max(Table[Unit]))
)If it give error for period then write like
Period Sales = Switch (True(),
Max(Table[Unit]) = "Month",
CALCULATE(
SUM('Sales Data'[Total Sales]),
DATESINPERIOD(Dates[Date], Max('Sales Data'[Start Date]), - 1*max(Table[Number]),Month )
),Max(Table[Unit]) = "Year",CALCULATE(
SUM('Sales Data'[Total Sales]),
DATESINPERIOD(Dates[Date], Max('Sales Data'[Start Date]), - 1*max(Table[Number]),Year)
),Max(Table[Unit]) = "Year",CALCULATE(
SUM('Sales Data'[Total Sales]),
DATESINPERIOD(Dates[Date], Max('Sales Data'[Start Date]), - 1*max(Table[Number]),DAY)
))
Table is having those 7 days etc
Thanks! The second version using SWITCH() worked. I added another section to include DAY and it worked exactly as hoped.
Much appreciated!
