The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Below is my date table, this is non standard date table of Fiscal year. So, i need to calculate MTD, YTD, Today, yesterday sales in power bi desktop.
Example of my dates for Aug month(FY 23) - {Start date - 31/7/22 and End date - 27/8/22}
FY - 23 start date is 03/7/2022 and end date is 01/07/2023.
Measures I've created:
Please help me to create MTD, YTD sales in power bi
Solved! Go to Solution.
Hi @Anonymous ,
I think you can try measures as below to calcualte MTD and YTD.
MTD =
CALCULATE (
SUM ( Sales[TotalCollectedRevenue] ),
FILTER (
ALL ( 'Sales' ),
Sales[Date] >= SELECTEDVALUE ( 'FiscalCalendar'[StartDate] )
&& Sales[Date] <= SELECTEDVALUE ( 'FiscalCalendar'[EndDate] )
)
)
YTD =
CALCULATE (
SUM ( Sales[TotalCollectedRevenue] ),
FILTER (
ALL ( 'Sales' ),
YEAR ( Sales[Date] ) = SELECTEDVALUE ( 'FiscalCalendar'[FiscalYear] )
)
)
Best Regards.
It's not about creating a proper date table, My project FY date table is the same i shown in the figure.
The New FY calendar is provided by client side. So I've created that type of date table. Please See below the calendar of start date and end date for each month of entire FY-23. So I've created entire date table based on this.
Hi @Anonymous ,
I think you can try measures as below to calcualte MTD and YTD.
MTD =
CALCULATE (
SUM ( Sales[TotalCollectedRevenue] ),
FILTER (
ALL ( 'Sales' ),
Sales[Date] >= SELECTEDVALUE ( 'FiscalCalendar'[StartDate] )
&& Sales[Date] <= SELECTEDVALUE ( 'FiscalCalendar'[EndDate] )
)
)
YTD =
CALCULATE (
SUM ( Sales[TotalCollectedRevenue] ),
FILTER (
ALL ( 'Sales' ),
YEAR ( Sales[Date] ) = SELECTEDVALUE ( 'FiscalCalendar'[FiscalYear] )
)
)
Best Regards.
@Anonymous do you want to do a zoom call and show me?
@Anonymous please join now:
https://us05web.zoom.us/j/87334819323?pwd=MHVtak8zVmZUNHlLWXF2MkZ3WHY1UT09
My date table in not in continues date range so that's why I'm unable to calculate MTD, YTD so on.....
Iam not able to use time intellegence functions with this date table.
Below is my date table and it's there for august month, so aug month contains july month's last date that is 31/7/22 and in the aug month, dates there till 27/8/22 only.
@Anonymous so why won't you create a proper date table so you could use time intelligence functions? In the articles there is code you could copy paste to create it.
@Anonymous hey, everything you need is in this article:
https://www.daxpatterns.com/standard-time-related-calculations/
This patten also support fiscal calendars with the limitations indicated there.
If your data does have these limitations then you can use this patten:
https://www.daxpatterns.com/custom-time-related-calculations/
This is the overview page with explanations of the different patterns:
https://www.daxpatterns.com/custom-time-related-calculations/
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
95 | |
93 | |
85 | |
68 | |
65 |
User | Count |
---|---|
241 | |
124 | |
120 | |
81 | |
79 |