YTD Sales does not show in the months where rows are missing

[Oat]

Hi guys,

May I seek your advices, I'm trying yo create YTD. However, in the raw data, some of rows are missing as there might be no sales in that month so when I put it in table, the data missing in those months as highlighted in yellow in the screenshot below,

I want to create Measure DAX to make YTD value shows every months

This is current Measure DAX that I'm using

Measure:

__YTD (Net Sales Value) =

CALCULATE(SUM ('Sales Report'[Monthly Sales]),

    'Sales Report'[Year]= MAX('Sales Report'[Year]) &&

    'Sales Report'[Date] <=MAX('Sales Report'[Date]))

Thank you in advance!

[Anonymous]

Hi, @Oat 

Thanks for Angith_Nair and bhanu_gautam reply. Did Angith_Nair give a solution to your latest problem that helped you? If it doesn't work around, you can share the pbix file without sensitive information for testing purposes, it will help you solve the problem faster.

Best Regards,
Yang
Community Support Team

[Oat]

Hi @bhanu_gautam ,

Thanks for your quick response! However, after I've tried it, the result seems does not work as I expected. I also want to keep the running total number showing every months (even though there are no data/no rows in source data for that month) as you may see in the screenshot that highlighted in yellow that there is no running total number shown.

In shorts, I'm just ttrying to figure out the way to get the result just like when I'm using DATESYTD that even though there are no rows/data in the source data but it still shows running total number as hihglihgted in green.

[Angith_Nair]

Hi @Oat 

Use this dax:

Running Total with Previous Month = 
VAR CurrentDate = MAX('Sales Report'[Date])  
VAR CurrentMonth = MONTH(CurrentDate)      
VAR CurrentYear = YEAR(CurrentDate)        
VAR CurrentMonthSales = 
    CALCULATE(
        SUM('Sales Report'[Monthly Sales]), 
        'Sales Report'[Date] = CurrentDate
    )

VAR PreviousMonthSales = 
    CALCULATE(
        SUM('Sales Report'[Monthly Sales]),
        PREVIOUSMONTH('Sales Report'[Date]) 
    ) 

VAR RunningTotal = 
    IF(
        NOT ISBLANK(CurrentMonthSales),
        CALCULATE(
            SUM('Sales Report'[Monthly Sales]),
            'Sales Report'[Date] <= CurrentDate
        ),
        CALCULATE(
            SUM('Sales Report'[Monthly Sales]),
            'Sales Report'[Date] <= PREVIOUSMONTH(CurrentDate)
        )
    )

RETURN
    RunningTotal

[Oat]

Hi @bhanu_gautam 

Thanks for your advices! it works for me, however, I also want to do half-year to date as well, so I'm not sure if you have any advices for this?

For example, from date 01 Jan until 30 Jun and start sum again from 01 Jul until 31 Dec (but some month the data might be missing as well but I still want the table to keep showing Half-Year to date as I will also use filter to filter some specific month and want to see what is the Half-Year to date at that time)

[bhanu_gautam]

@Oat , For half year you can use datesbetween

__HYTD (Net Sales Value) =
VAR CurrentDate = MAX('Sales Report'[Date])
VAR StartOfYear = DATE(YEAR(CurrentDate), 1, 1)
VAR StartOfHalfYear = IF(MONTH(CurrentDate) <= 6, StartOfYear, DATE(YEAR(CurrentDate), 7, 1))
RETURN
CALCULATE(
SUM('Sales Report'[Monthly Sales]),
DATESBETWEEN(
'Sales Report'[Date],
StartOfHalfYear,
CurrentDate
)
)

[bhanu_gautam]

@Oat , Try using

__YTD (Net Sales Value) =
CALCULATE(
SUM('Sales Report'[Monthly Sales]),
DATESYTD('Sales Report'[Date])
)