Forum Discussion

amitchandra's avatar
amitchandra
Regular Visitor
8 years ago
Solved

How to plot YTD months on a bar chart

So, I have this month filter slicer in my report. I have another visual in a report - a bar chart. The chart plots a measure on the Y axis and Fiscal month on the X axis. The month on Y axis changes ...
Need Help
  • danextian's avatar
    danextian
    8 years ago

    Hi amitchandra,

     

    My apporach in this situation would be to create a disconnected table (one that doesn't have any relationships with other tables) and use a helper column.

     

    I would create  calculated column in Date table called Rolling Months.

     

    Rolling Months = 
VAR MAX_DATE_ =
    CALCULATE ( MAX ( 'Date'[Date] ), ALL ( 'Date' ) ) //calculates  the max date of the table, will not be affected by on-page slicers and cross filters
RETURN
    DATEDIFF ( 'Date'[Date], MAX_DATE_, MONTH )
//calculates the difference in months from max date, the latest month will return zero

    I would create a calculated table based of my existing Date table.

     

    Month Table =
ALL ( 'Date'[Month], 'Date'[Rolling Months] )

     

     

    Then I would create a measure that gets filtered based on the selection from the disconnected table and use this measure  in the visuals.

     

    MeasureYTD = 
CALCULATE (
    SUM ( 'Fact'[Measure] ),
    FILTER ( 'Date', 'Date'[Rolling Months] >= [Rolling Month Selected] )
)

    These would result to something like this:

     

    For the PBIX, refer to this link https://drive.google.com/file/d/1TBd7w3_bcV4E0oydgrB_ZANL60srZUBg/view?usp=sharing

danextian's avatar
danextian
Icon for Super User rankSuper User
8 years ago

Hi amitchandra,

 

My apporach in this situation would be to create a disconnected table (one that doesn't have any relationships with other tables) and use a helper column.

 

I would create  calculated column in Date table called Rolling Months.

 

Rolling Months = 
VAR MAX_DATE_ =
    CALCULATE ( MAX ( 'Date'[Date] ), ALL ( 'Date' ) ) //calculates  the max date of the table, will not be affected by on-page slicers and cross filters
RETURN
    DATEDIFF ( 'Date'[Date], MAX_DATE_, MONTH )
//calculates the difference in months from max date, the latest month will return zero

I would create a calculated table based of my existing Date table.

 

Month Table =
ALL ( 'Date'[Month], 'Date'[Rolling Months] )

 

 

Then I would create a measure that gets filtered based on the selection from the disconnected table and use this measure  in the visuals.

 

MeasureYTD = 
CALCULATE (
    SUM ( 'Fact'[Measure] ),
    FILTER ( 'Date', 'Date'[Rolling Months] >= [Rolling Month Selected] )
)

These would result to something like this:

 

For the PBIX, refer to this link https://drive.google.com/file/d/1TBd7w3_bcV4E0oydgrB_ZANL60srZUBg/view?usp=sharing

MaxNovak's avatar
MaxNovak
Icon for Resolver I rankResolver I
7 years ago

Hi,

 

I want to make a follow up to danextian great solution for plotting YTD chart. The solution is great but if you need to

filter YTD through different months and years it doesn't work. 

 

You have to add these things:

 

1) I would create three calculated columns in Date table called Rolling Years, Rolling Months and Year Month.

 

Rolling Years = 
VAR MAX_DATE_ =
    CALCULATE ( MAX ( 'Date'[Date] ), ALL ( 'Date' ) ) 
RETURN
    DATEDIFF ( 'Date'[Date], MAX_DATE_, YEAR )

 

Rolling Months = 
VAR MAX_DATE_ =
    CALCULATE ( MAX ( 'DimDate'[Date] ), ALL ( 'DimDate' ) ) 
RETURN
    DATEDIFF ( 'DimDate'[Date], MAX_DATE_, MONTH )

 

 

YearMonth = FORMAT(DimDate[Date],"yyyy-mmm")

 

2) I would create a calculated table based on my existing DimDate table.

 
Date = ALL ( 'DimDate'[Rolling Months],DimDate[Rolling Year],DimDate[YearMonth] )
3) I would create two measures in the new Date table. 
 
Rolling Year Selected = MIN('Month'[Rolling Year])
Rolling Month Selected = MIN('Month'[Rolling Months])

4) Finally, I would create a measure that gets filtered based on the selection from the disconnected table and use this measure in the visuals.

 

MeasureYTD = 
CALCULATE (
    SUM ( financials[ Sales] ),
    FILTER ( 'DimDate', 'DimDate'[Rolling Months] >= [Rolling Month Selected] ),FILTER(DimDate,DimDate[Rolling Year] = [Rolling Year Selected])
)