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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Datagulf
Responsive Resident
Responsive Resident

Month minus Finacial year

Hello, Wonderful community. I am trying something interesting here and wanted your approach. So we have a dynamic formula that calculates the number of months already passed in the financial year. Then it can multiply this number with a monthly value to get the YTD target.  

So my financial year starts in April. If this value we need is in a column, how best would one go around that. 

2 REPLIES 2
amitchandak
Super User
Super User

@Datagulf , Try with help from time intellignece and date table

 

YTD Months= CALCULATE(distinctcount('Date'[Month Year]),DATESYTD('Date'[Date],"03/31"))

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

 

Target = [MTD Sales] * [YTD Months]

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Hey, @amitchandak , thanks,  though the MTD Sales return Blank. No idea why. I have connected the Date table to the data. No errors..

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors