How to create chart monthly based on YearMonth filter?

[YunJ]

Hello,

I would like to make bar and two line charts(MoM, YoY). For example, when YearMonth=201507, the horizontal axis of the bars and lines should be 201501 to 201512, but only show values from 201501 to 201507. Is that possible in pbi? I attached the pbix file in onedrive

https://1drv.ms/u/s!ApmbqlXBl3vGgQ_ybCdD9qNmsQjR.

Thanks for your help.

[Anonymous]

Hi @YunJ,

IF you want to achieve the selector effect instead of filter effects, I'd like to suggest you use not related table date field as the source of a slicer.

You can write a measure to extract the selected year month and compare it with current table records, then you can use this measure on 'visual level filter' to filter your chart records.

Measure filter =
VAR selected =
    MAX ( 'selector'[Date] )
VAR currDate =
    MAX ( 'Table'[Date] )
RETURN
    IF (
        YEAR ( currDate ) = YEAR ( selected )
            && MONTH ( currDate ) <= MONTH ( selected ),
        "Y",
        "N"
    )

Regards,

Xiaoxin Sheng

[YunJ]

Hello, could you provide a sample pbi file? It's a bit abstract for me to understand...

[Anonymous · Accepted solution]

Hi @YunJ,

I modify your sample file to turn off the relationship and add a measure filter on your chart 'visual level filter', you can check the attached sample file to know more about this.

Regards,

Xiaoxin Sheng

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[amitchandak]

You can use time intelligence and date calendar for that

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")))


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))

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/