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 :)

    • 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])
)