How to show values for previous year and selected year in one chart?

[YunJ]

Hello,

I would like to make two bars (in one visual so that they can have the same axis). One is values for previous YearMonth, another is for selected YearMonth. Also, is it possible to add a filter MTD/YTD?

For example,

when I select 201507, and select YTD, one bar will be the sum value of 201401~201407, and another bar will be  201501~201507. 

when I select 201507, and select MTD, one bar will be the sum value of 201407, and another bar will be  201507. 

I attached pbix file in onedrive. https://1drv.ms/u/s!ApmbqlXBl3vGgQ_ybCdD9qNmsQjR 

Also, if the above can achieve, may be the following table can also realize?(e.g.when select 201507)

Thanks a lot!

[v-frfei-msft · Accepted solution]

Hi @YunJ ,

I have created a sample for your reference.

Measure = 
VAR sel =
    SELECTEDVALUE ( 'date'[Yearmonth] )
VAR pre =
    ( YEAR ( MAX ( 'date'[Date] ) ) - 1 ) * 100
        + MONTH ( MAX ( 'date'[Date] ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER (
            'Table',
            'Table'[date] IN VALUES ( 'date'[Date] )
                || YEAR ( 'Table'[date] ) * 100
                    + MONTH ( 'Table'[date] ) = pre
        )
    )

Measure 2 = 
VAR m =
    MONTH ( MAX ( 'date'[Date] ) )
VAR k =
    FILTER ( 'Table', 'Table'[Month] <= m )
RETURN
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[year] ),
            'Table'[Month] <= MAX ( 'Table'[Month] )
        ),
        KEEPFILTERS ( k )
    )

Pbix as attached.

[YunJ]

Hello Frank,

Thanks for your solution. I'm a little new to power bi. May I ask some questions about what you did?

For this Measure, I'm wondering 

• Why use IN VALUE？
• What is "||" for?
• I didn't find where you use values for sel, so why there is also current Yearmonth value in the bar chart?

For this measure2, 

why use ALLEXCEPT and KEEPFILTERS? I created another Measure3 only used FILTER and have the same result.

Thanks a lot for your patience.

Yun

[amitchandak]

You can use time intelligence with date calendar. Have the month year format there

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year + 3 week behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(dateadd('Date'[Date],-1,Year),-21,Day))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))
MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/