Have graph reflect forecast based on Month-end Date

[atowriss1]

Hi,

I need to create an actuals + forecast graph that will change based on the date selected.  I have a graph that has actual + forecast, but I want it to select the correct version of the forecast, as we update quarterly.

So, if I choose March, it will show Jan-Mar actuals, and Apr-Dec as of 3/31  If I choose Aug, it will show Jan-Aug actuals, with Sept-Dec FC as of 9/30.  In my forecast table, I have a column for ME Date and Forecasted Date in my Graph FC table .  Below is what the graph looks like now. I tried to update my calculated measure as below, but nothing changes.  I believe I have compounding issues here...and am lost.  I always want my graph to show Jan-Dec

Additionally, here is a sample of my data

TTL 2022 =

VAR LastSalesDate=

SELECTEDVALUE('Date Slicer'[Date]

)

VAR _Actuals=[ Graph Volume CY]......(This is a YTD Caculated Measure based on Actuals table)

VAR FC=

CALCULATE(

    [Graph Volume FC],...(Graph Volume FC=

CALCULATE(sum('0922 FC'[Volume] ),'0922 FC'[ME Date]=SELECTEDVALUE('Date Slicer'[Date]),'Budget Hierarchy'[PBI Roll-Up]="SA",

USERELATIONSHIP('0922 FC'[Forecasted Month],'Dates_Table Indep'[Date])

    USERELATIONSHIP('0922 FC'[ME Date],'Dates_Table Indep'[Date]),

    Filter(

        'Dates_Table Indep',

        'Dates_Table Indep'[Date]>LastSalesDate)

    )

Return FC+_Actuals

[v-xiaosun-msft]

Hi @atowriss1 ,

According to your description, I wonder that whether the forecast value is the sum of the actual values? And can you describe more clearly about the line chart? 

From the DAX expression you gave, I did not find any problems here, you may have to combine your visual field placement and table model to troubleshoot the problem, if it is convenient, you can upload your pbix file without  private data for our further study.

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[atowriss1]

Hi,

I am not looking to sum, just graph the month's actuals and then forecast if not actuals.  Attached is a screenshot of my graph and table data.  To get this graph, it's 2 lines overlapping- CY (aka Actuals) and Total(Actuals + FC) with the FC being dashed.  These are the related measures I have referencing my smaple data here, and it works to create the graph, but it's not dynamic.  What I want it to do is when a month-end date is chosen on the slicer, I want to graph the actuals by month, and use the slicer date to look at the ME date in the FC file and graph the forecasted months based on that month end date, as we update quarterly at a minimum.

Ex:  The slicer date is 3/31/2022.  I want the graph to show Jan, Feb and March actuals- which should happen with my graph CY measure.  For my forcasted values, I want it to look at my FC data for ME 3/31/2022 and graph April, May, Jun, through Dec (forecasted Month)

I'm not sure this is possible, but if it is, I know I need to change my graph volume fc measure, I just don't know how.

I appreciate your help!

Total 2022 =

VAR LastSalesDate=

CALCULATE(

MAX('Vol Actuals'[ME Date]),

REMOVEFILTERS()

)

VAR _Actuals=[Strat Acct Graph Volume CY]

VAR FC=

CALCULATE(

    [Strat Acct Graph Volume FC],

    USERELATIONSHIP('0922 FC'[Forecasted Month],'Dates_Table Indep'[Date]),

    Filter(

        'Dates_Table Indep',

        'Dates_Table Indep'[Date]>LastSalesDate)

    )

Return FC+_Actuals

Strat Acct Graph Volume FC = CALCULATE(sum('0922 FC'[Volume] ),'Budget Hierarchy'[PBI Roll-Up]="Strategic Accounts",USERELATIONSHIP('0922 FC'[Forecasted Month],'Dates_Table Indep'[Date])

Strat Acct Volume CY = TOTALYTD(CALCULATE(sum('Vol Actuals'[Volume]),'Budget Hierarchy'[PBI Roll-Up]="Strategic Accounts"),'Vol Actuals'[ME Date])