Time Intelligence

[KendallBrown13]

I have a unique issue that I'm hopeful someone on this forum can help me answer. 

Here is an image of my dashboard. 

Here is the issue at hand - when I filter the Year slicer in the top left corner, the rest of the dashboard filters properly except for the chart. The chart has two bars and two lines, one for the current period and one for last year same period. When I change the year to say 2020, the previous year line in the chart disappears. 

What I want to happen is for the Year Slicer in the top corner to be representative of "current year". Meaning that when that slicer is changed to reflect a year, that the graph on the bottom uses the that year minus one as the previous period. 

Hoping someone knows how to fix this! 

Thanks!

[v-yanjiang-msft · Accepted solution]

Hi @KendallBrown13 ,

There're two possible reasons why the previous year value disappear, first maybe it return blank, which because of there's no value in the previous year in the sample or a incorrect formula, second maybe the previous year value and the current year value coincide. You can convert the chart to a table visual to see the specific value.

Additionally, the MonthYear column in your sample is already a date format and does not need to add .[Date] after it in the formula.

I create a sample and here's my solution.

Pre =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[MonthYear]
            = DATE ( YEAR ( SELECTEDVALUE ( 'Table'[MonthYear] ) ) - 1, MONTH ( MAX ( 'Table'[MonthYear] ) ), DAY ( MAX ( 'Table'[MonthYear] ) ) )
    )
)

It get correct result in my sample.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@KendallBrown13 , Hope you are using a date table and measure like

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

Slicer and visual should use the period from date table.

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[KendallBrown13]

This first screenshot is the output for the Chart in the Dashboard. 

These are the formulas for the two calculated fields. The LY Sales is for the bars and the LY Cost of Sales is for the lines. 

This next screenshot is for the matrix in the top left corner.  

Please let me know if this is enough information! It would take a lot of time for me to clean the pbix file but if that's required I can do so. 

[amitchandak]

@KendallBrown13 , Join your month year date column with the date table and then create the measure. Make sure slicer and visual also use the period from date table

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...

[KendallBrown13]

I don't have a date table in my model. Is there a recommended way I should set this up?