Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
There is a specific report on additional sales from system which only generates job to date sales result but not by month.
I have trouble creating Year To date and Month to date from JTD which really frustrates me.
Here is the table. All data are in Job to date value
Report date | Project | Total Price |
1/3/2023 | Darius | 350000 |
1/2/2023 | Darius | 280000 |
1/1/2023 | Darius | 200000 |
1/12/2022 | Darius | 180000 |
1/11/2022 | Darius | 130000 |
I first get Previous Year JTD :
March 23 for example should be 350k -180k = 170k not 350k.
Hey @noseriya ,
I recommend calculating the monthly values, so that you can calculate the YTD values following the pattern of the article I recommend in my first article.
Not sure if this is correct, but the following DAX statement can be used to create a calculated column:
current price =
var currentReportDate = [Report date]
var currentYear = YEAR( currentReportDate )
var previousReportDate = EOMONTH( EOMONTH( currentReportDate , 0) , -2) + 1
var currentProject = [Project]
var currentPrice = [Total Price]
var previousPrice =
SUMX(
FILTER(
'Table'
, 'Table'[Project] = currentProject
&& 'Table'[Report date] = previousReportDate
)
, 'Table'[Total Price]
)
return
IF( YEAR(currentReportDate ) = YEAR( previousReportDate)
, currentPrice - previousPrice
, currentPrice
)
And a screenshot from the result:
Hopefully, this provides an idea of how to get started.
Regards,
Tom
Hey @noseriya ,
and a DAX statement to create a calculated column using one OFFSET, one of the windowing functions that have been introduced in December 2022:
current price (OFFSET) =
var currentyear = YEAR( 'Table'[Report date] )
var currentPrice = 'Table'[Total Price]
var monthlyPrice =
CALCULATE(
SUM( 'Table'[Total Price] )
,
OFFSET(
-1
,
FILTER(
SUMMARIZE(
'Table'
, 'Table'[Project]
, 'Table'[Report date]
)
, YEAR( 'Table'[Report date] ) = currentyear
)
, ORDERBY( [Report date] , ASC )
,
, PARTITIONBY( [Project] )
)
, ALL( 'Table' )
)
return
IF( NOT( ISBLANK( monthlyPrice ) )
, currentPrice - monthlyPrice
, currentPrice
)
Regards,
Tom
Hey @noseriya ,
I recommend reading this article: Time patterns – DAX Patterns. This article covers almost everything related to time intelligence calculations.
Next, add an extra date/calendar table to your data model.
Hopefully, this gets you started.
Regards,
Tom
Hey thanks for the answer. What I am struggling is that there seems to be no equivalent to my scenario ie my report is configured to JTD results from the system and the system is unable to generate MTD at all. I really want to split it at least to YTD or even MTD, and its seems I am stuck.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |