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
adnanarain
Helper V
Helper V

Actuals Running total till selected month and forecast after the selected month

Dear All,

I am trying to show running total of actuals till the selected month and forecast after the selected month in one line chart. Here is the file i am using:

 

Pbix file 

 

Thank you in advance for the help.

1 ACCEPTED SOLUTION

Hi @adnanarain 

 

Is this what you want? Download the attachment to see details. I add a Financial Month column in DimDate table and four new measures. 

21111202.jpg

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

6 REPLIES 6
amitchandak
Super User
Super User

@adnanarain , Try a measure like

 

var _max = maxx(allselected('Date'),'Date'[Date])
return
CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date]) && date[date] <=_max)) +
CALCULATE(SUM(forecast[forecast Amount]),filter(all(date),date[date] <=max(date[Date]) && date[date] >_max))

 

Common date table joined to you table and forecast and that is used on axis

@amitchandak  Thanks Amit,

I have already tried this but this is not working. I need a year and month slicer with single value selection and based on month selection. If you can please download the file and you will get clear idea

Hi @adnanarain 

 

Sorry I'm a little confused about the data table. In this table, there is a [Financial Date] column, [Month for Financial Date] and [Year for Financial Date]. Are the [Month for Financial Date] and [Year for Financial Date] columns identical with the [Fiscal Month] and [Fiscal Year] in DimDate table or are they just calendar month and year?

21111003.jpg

 

Do you look for a solution to get a similar output to the line chart in this blog?

 

Best Regards,
Community Support Team _ Jing

@v-jingzhang thanks for your reply. Financial month is the column to use. Ignore financial date

Hi @adnanarain 

 

Is this what you want? Download the attachment to see details. I add a Financial Month column in DimDate table and four new measures. 

21111202.jpg

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

@v-jingzhang Thank you so much.

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.