Need column chart that shows a measure for 3 different time periods

[LisaK]

I have a request to build a column chart that shows the value of a measure for the last 7 days, last 28 days and last 365 days.  Any suggestions on the best way to approach this?  Below is an example of what I'm trying to reproduce (the line is the goal line)...

[LisaK]

Great ideas!  Thanks everyone.  I'll give them a try.

[v-eqin-msft · Accepted solution]

Hi @LisaK ,

According to my understanding ,you want to calculate sum of value based on different time periods, right?

You could use the following formula:

last7 =
CALCULATE (
    SUM ( 'Last Period'[Value] ),
    FILTER ( 'Last Period', DATEDIFF ( 'Last Period'[Date], NOW (), DAY ) <= 7 )
)

last28 =
CALCULATE (
    SUM ( 'Last Period'[Value] ),
    FILTER ( 'Last Period', DATEDIFF ( 'Last Period'[Date], NOW (), DAY ) <= 28 )
)

last365 =
CALCULATE (
    SUM ( 'Last Period'[Value] ),
    FILTER ( 'Last Period', DATEDIFF ( 'Last Period'[Date], NOW (), DAY ) <= 365 )
)

Due to the measure cannot be used as X-axis, so you could create a new column with a value of "last 7" ,"last 28" and "last 365".Then use the column as X-axis to create a column chart.
 

Then use Switch() to sum values.

sum value =
SWITCH (
    SELECTEDVALUE ( 'for X-axis'[last period] ),
    "last 7", [last7],
    "last 28", [last28],
    "last 365", [last365]
)

My visualizations look like this:
 

Is the result what you want? If not, please upload some data samples and expected output.
Please do mask sensitive data before uploading.

Best Regards,
Eyelyn Qin

[Anonymous]

Try a Calculation Group

https://docs.microsoft.com/en-us/analysis-services/tabular-models/calculation-groups?view=asallprodu...

[lbendlin]

You need a table with this structure

Index	Label
7	L7
28	L28
365	L365

Mark the index column as whole number

Then you can add a measure to that table that based on the index computes the values you need.

Sort the Label column by the Index column.

Now you can add the Label to the legend, and the measure result to the values.