Dynamically Calculating Sales with Respect to Day Granularity

[djkoenig]

Hello Experts, 

First and foremost, thank you for the time to read. I really appreciate you all.

So, there are about 100 ways to calculate YTD, QTD, MTD and a few less for dynamically calculating YTD for Prior Year, or QTD for Prior Quarter Last Year, etc... Unfortunately, I have stumbled across a bevy of issues and have not been able to succesfully complete the calculation to the required standard.

In Option 1, the main issue is that the formula is not calculating the "stub period" and instead summing the entirety of the month, quarter, etc. without respect to the day. In options 2 & 3, there are different problems, which will be detailed further. 

Also, before we begin, one caveat is that I would like my measures to display on cards, thus providing a summary view at a glance.

Example Issue in Numbers

Sales YTD (Jan 1st, 2023 - Nov 17th, 2023) = 200M

Total Sales 2022 = 250M

Total Sales YTD Prior Year (Jan 1st, 2022 - Nov 17th, 2022) = ???

Option 1) DATEADD - Where [Sales] is a previously calculated measure for the summation of all sales. 

Sales YTD = 
CALCULATE(
    [Sales],
    DATESYTD(Date[Dates])
)

Sales YTD Previous = 
CALCULATE(
    [Sales YTD],
    DATEADD(Date[Dates],-1,YEAR)
)

This code was picked up from Power BI DAX: Previous Month-to-Date, Quarter-to-Date, and Year-to-Date Calculations - RADACAD and suggests that it should pick up the stub period. That has not been my experience, perhaps I am overlooking something.

Option 2) SAMEPERIODLASTYEAR

YTD Y1 Dynamic = Calculate(Sum(YY1_BI_Customer_Sales[NetAmount]), SAMEPERIODLASTYEAR(YY1_BI_Customer_Sales[ActualGoodsMovementDate - Copy]), YY1_BI_Customer_Sales[Offset Year]=-1)

Now this does work for the stub period issue, mostly. When I try to filter by a slicer on something like Product or Customer I receive an error stating that the range is not contigous. Now, that is true. I do not have a sale everyday of the year for every product.

Yet, my thought was that if I built a calendar table and hooked the Sold Date/ActualGoodsMovementDate - Copy with a 1:N relationship to the calendar table, then my range would be continuous. This change does allow me to filter by product and customer, however my total is now the total for the full previous year, instead of the stub period. I only swapped ActualGoodsMovement - Copy with Date from my Calendar Table. 

I have also tested with just a measure, instead of a calculated column in the first arguement for Sales Amount. That appears to not matter.

Additionally, removal of the offset year clause caused the summation to be for All Time, despite the SAMEPERIODLASTYEAR filter. This occurred regardless of which date I was using, with the ActualGoodsMovementDate providing the stub period sum of ALL of the years.  

Option Three) Custom Dax Calculation

This is probably the closest solution. However, I have to manually add filters that would need to be changed periodically. My intention is for this report to be fully dynamic/automatic. My hope is that we can adjust Code Snippet One (below) and find a solution.

     vs.   

I have also added Code Snippet Two and Three for reference, as they play into one. 

Code Snippet One

Sales PY = 
VAR MonthsOffset = 12
RETURN IF (
    [ShowValueForDates],
    SUMX (
        SUMMARIZE ( 'Date', 'Date'[Year Month Number] ),
        VAR CurrentYearMonthNumber = 'Date'[Year Month Number]
        VAR PreviousYearMonthNumber = CurrentYearMonthNumber - MonthsOffset
        VAR DaysOnMonth =
            CALCULATE (
                COUNTROWS ( 'Date' ),
                ALLEXCEPT (
                    'Date',
                    'Date'[Year Month Number], 
                    'Date'[Working Day],        
                    'Date'[Day of week]         
                )
            )
        VAR DaysSelected =
            CALCULATE (
                COUNTROWS ( 'Date' ),
                'Date'[DateWithSales] = TRUE
            )
        RETURN IF (
            DaysOnMonth = DaysSelected,
 
            -- Hopefully, selection of all days in the month
            CALCULATE (
                [Sales Amount],
                ALLEXCEPT ( 'Date', 'Date'[Working Day], 'Date'[Day of Week] ),
                'Date'[Year Month Number] = PreviousYearMonthNumber
            ),
             
            -- Hopefully, partial selection of days in a month
            CALCULATE (
                [Sales Amount],
                ALLEXCEPT ( 'Date', 'Date'[Working Day], 'Date'[Day of Week] ),
                'Date'[Year Month Number] = PreviousYearMonthNumber,
                CALCULATETABLE (
                    VALUES ( 'Date'[Day of Month Number] ),
                    ALLEXCEPT (                      
                        'Date',                      
                        'Date'[Day of Month Number], 
                        'Date'[Date]                 
                    ),
                    'Date'[Year Month Number] = CurrentYearMonthNumber,
                    'Date'[DateWithSales] = TRUE
                )
            )
        )
    )
)

Code Snippet Two

ShowValueForDates = 
VAR LastDateWithData =
    CALCULATE (
        MAX ( YY1_BI_Customer_Sales[ActualGoodsMovementDate] ),
        REMOVEFILTERS ()
    )
VAR FirstDateVisible =
    MIN ( 'Date'[Date] )
VAR Result =
    FirstDateVisible <= LastDateWithData
RETURN
    Result

Code Snippet Three

DateWithSales = 'Date'[Date] <= MAX ( YY1_BI_Customer_Sales[ActualGoodsMovementDate] )

You would think that right after the CALCULATE([SalesAmount] clause there would be an easy way to set this up. Yet, when I try to add additional logic I receive a warning indicating that it "cannot evaluate True/False logic", so my syntax for the filter within the calculation cannot be correct. 

Alternatively, there may be a simpler way altogether. I doubt this will be the last time I need to calculate YTD, QTD, MTD in comparison to previous periods in different years. I am all ears if there is a more scalabe way.

Please let me know if you have any questions or need anything further, although it will be difficult to share this PBIX as it does contain some sensitve info. 

Thank you all once again! 

[sjoerdvn · Accepted solution]

Sales YTD Previous = 
CALCULATE(
    [Sales],
    DATEADD(DATESYTD('Date'[Dates]),-1,YEAR)
)

Note that this works only if there is a filter active that filter out dates after today, like a relative date filter on your visual/page/report

[djkoenig]

@sjoerdvn Thank you for that one sentence explanation. That was what I was missing. Really made that too hard on myself. Solution accepted! 😁