Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Pbiuserr
Post Prodigy
Post Prodigy

How to show actual and forecast in one matrix

Hello,

I've prepared visually what I want to represent, horizontal way to not it let be too long. Basically what I want to do is to bring "Actual" values up until the user selection (in the example Jun-2022) and fill rest of the dates until current month with forecast. I have standard data model - dim table with dates (month year as the slicer) and  the actual and forecast is stored in fact table as "expenses" with assigned property either "actual" or "forecast" they're calculated as measure the way CALCULATE ( SUM ( facttable[Expenses], EXP_TYPE = "actual" (or "forecast) ). Is it something I can achieve? I know it will be in two separate columns

Pbiuserr_5-1675164973619.png

 

 

 

 

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Pbiuserr , make sure the slicer filter is coming from an independent date tbale. Then have meausre like

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
return

if(max(Date[Date]) <=_max, [Actual], [Forecast])

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@Pbiuserr , make sure the slicer filter is coming from an independent date tbale. Then have meausre like

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
return

if(max(Date[Date]) <=_max, [Actual], [Forecast])

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.