Forum Discussion

hmil's avatar
hmil
Frequent Visitor
7 years ago
Solved

Column for forecast stacked bar chart

I've made this table as a sample dataset for the project I am working on.  I currently have the units used and requested columns but would like to build the "forecast column".  My vision is to have a stacked bar chart that would show how many units we should anticipate using in the future.  The forecast column needs to be the number of units used on the last day of the month of the previous month.  Any ideas on how to do this?  Thanks!

 

Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    7 years ago

    hmil,

     

    You may add the following measure.

    Measure =
VAR d =
    MAX ( Table1[DATE] )
RETURN
    MAXX (
        TOPN (
            1,
            FILTER (
                ALLSELECTED ( Table1 ),
                Table1[DATE] < DATE ( YEAR ( d ), MONTH ( d ), 1 )
            ),
            Table1[DATE], DESC
        ),
        Table1[Units Used]
    )

1 Reply

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    7 years ago

    hmil,

     

    You may add the following measure.

    Measure =
VAR d =
    MAX ( Table1[DATE] )
RETURN
    MAXX (
        TOPN (
            1,
            FILTER (
                ALLSELECTED ( Table1 ),
                Table1[DATE] < DATE ( YEAR ( d ), MONTH ( d ), 1 )
            ),
            Table1[DATE], DESC
        ),
        Table1[Units Used]
    )