Abnormal Calendar Time Intelligence issues

[barf003]

Attempting to built time intelligence patterns for a sales calendar that varies by Country and Product.    I would like to be able to select a date and have QTD, YTD and Rolling dates calculate for each of the Product/Country combinations even if no sales were recorded for that "date".    I have attached a link to an example pbix.    Please let me know if I can provide additional details.   

This is the dax I am currently using, but it the sales only show up if there is a sale on that specific date.   

QTD Sales =

VAR QuarterStart = MAXX(values(DailySales[SalesLink]),Max('Calendar'[QUARTER_START_DATE_SALES]))

VAR QuarterEnd = Maxx(values(DailySales[SalesLink]),SELECTEDVALUE('Calendar'[SALES_ROLLUP_DATE]))

VAR DatestoUse = Filter(

                        ALL('Calendar'),

                        'Calendar'[SALES_ROLLUP_DATE]>=QuarterStart&&

                         'Calendar'[SALES_ROLLUP_DATE]<=QuarterEnd)

VAR RESULT = CALCULATE([Sales],DatestoUse)

RETURN

RESULT

Pbixlink 

[AlexisOlson]

Your Calendar table is highly non-standard. A date table should only have a single row per day, but you've got about ten times that much since you have countries and brands mixed in there too.

That said, this might get you closer:

QTD Sales =
VAR QuarterStart = MAX ( 'Calendar'[QUARTER_START_DATE_SALES] )
VAR QuarterEnd = SELECTEDVALUE ( 'Calendar'[SALES_ROLLUP_DATE] )
VAR DatestoUse =
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[SALES_ROLLUP_DATE] >= QuarterStart
            && 'Calendar'[SALES_ROLLUP_DATE] <= QuarterEnd
    )
VAR RESULT =
    CALCULATE ( [Sales] + 0, DatestoUse )
RETURN
    RESULT

The start and end are no longer dependent on the sales table and the "+ 0" forces it to always return a value instead of blank.

[barf003]

If at all possible I would like to keep the calendar in its current format.   If I was to modify the calendar I would have 4 or 5 calendar tables as the start and end dates are all different for MTD, QTD, YTD, Rolling etc based on sales dates.  

[barf003]

Thanks for your reply.   It doesn't quite get me to what I am trying to accomplish.   When I select a date like 9/15/2023 using this dax I only get zeros.   What I was hoping to accomplish was to show QTD for all product and country combinations up to 9/15/2023 regardiless if there are sales on that day or not.    Do you think it would be better to create a link table that only has a single row per day?  

[AlexisOlson]

Yes, definitely better to have a proper calendar table.