Forum Discussion

rpinxt's avatar
rpinxt
Solution Sage
3 years ago
Solved

Automatically exclude months in graphic

Hi All,

We have a graph showing data by month. Nothing veru special.

Now the thing is that data is not yet complete and can be up to 2 months before that data is complete.

 

We are looking for a way that the vizual can show us data excluding the last 2 months it will find in the source.

To make it a bit more visual:

So in this data set there is data up till August. But August and July we would like to not show.

Of course we can do that in the filter, but then we would have to manually change this every month.

Was trying to see if it could automatically done with 'relative date' option or something.

But can't find anything.

Maybe somebody has an idea how to achieve this (fairly easy)?

Need Help
  • ddpl's avatar
    ddpl
    3 years ago

    rpinxt ,

     

    1. Create a calculated column.

    Month = 'Table'[Date].[MonthNo]
     
    2. Create another calculated column.
    Flag = 
    var _mm = MAX('Table'[Month])
    return
    SWITCH(
        TRUE(),
        MONTH('Table'[Date]) = _mm || MONTH('Table'[Date]) = _mm-1, "Exclude",
        "Include"
    )
     
    3. Put the Flag column in the visual's filter section and tick mark "Include".

2 Replies

  • ddpl's avatar
    ddpl
    Solution Sage
    3 years ago

    rpinxt ,

     

    1. Create a calculated column.

    Month = 'Table'[Date].[MonthNo]
     
    2. Create another calculated column.
    Flag = 
    var _mm = MAX('Table'[Month])
    return
    SWITCH(
        TRUE(),
        MONTH('Table'[Date]) = _mm || MONTH('Table'[Date]) = _mm-1, "Exclude",
        "Include"
    )
     
    3. Put the Flag column in the visual's filter section and tick mark "Include".
  • rpinxt's avatar
    rpinxt
    Solution Sage
    3 years ago

    Thanks ddpl see what you are doing there.

    Bit more complicated for me as I am using a calculated date table with multiple sources.

    But was able to make it work using the max of the source of the visual and put include and exclude on the month numbers of the date table. 😁
    (months of other years posed no problem as the visual looks only at 1 year)