Forum Discussion

KendallBrown13's avatar
KendallBrown13
Frequent Visitor
4 years ago
Solved

Time Intelligence

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!

 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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.

     

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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.