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

Would you mind posting a sample data?

  • amitchandra's avatar
    amitchandra
    Regular Visitor
    8 years ago

    Can't post the actual data but below should give an idea -

     

    Fact Table -

     

    DateIDMeasure
    2017010234
    2017013063
    201702146345
    20170325635
    20170316824
    20170421524

     

    Date Table -

     

    DateIDDateMonth
    201701022/01/2017Jan
    2017013030/01/2017Jan
    2017021414/02/2017Feb
    2017032525/03/2017Mar
    2017031616/03/2017Mar
    2017042121/04/2017Apr

     

    Relation b/w them 'Date Table'[DateID] -> 'Fact Table'[Date[ID]

     

    I am dragging 'Fact Table'[Measure] on the Values, and 'Date Table'[Month] on the Axis.

    • 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

      • amitchandra's avatar
        amitchandra
        Regular Visitor
        8 years ago

        Thanks Dan, I will get to this a little later than expected. Keep you posted on how I go. Thanks much :)