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
noseriya
Frequent Visitor

How do I create Month to date and Year to date from a Job to date sales report

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 dateProjectTotal Price
1/3/2023Darius350000
1/2/2023Darius280000
1/1/2023Darius200000
1/12/2022Darius180000
1/11/2022Darius130000

 

I first get Previous Year JTD : 

Prior year JTD = CALCULATE(sUM(Sheet1[Total Price]),Sheet1[Report date]=Date(2022,12,31))
 
Then total sales sum up
Total Price inputs = calculate(sum(Sheet1[Total Price]))
 
 
 
Then YTD
 
Total Price YTD = [Total Price inputs]-[Prior year JTD]
 
For some reason YTD keep on failing
 
noseriya_0-1680407455399.png

March 23 for example should be 350k -180k = 170k not 350k. 

4 REPLIES 4
TomMartens
Super User
Super User

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:
image.png
Hopefully, this provides an idea of how to get started.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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. 

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.