Abnormal Calendar Time Intelligence issues

DAX DAX Calculations
1

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
AcmeExample.pbix (337.7 KB)

2

@Michael4,

Is your date table a proper date table?

3

Thanks for you reply.

No - There are 4 products that can only be sold on certain days in their respective countries.
I could split the calendar into its 4-5 variants but was hoping to come up with some creative dax to not have to have 4 or 5 different calendars. The one calendar option is ideal as it is maintained by the sales team. Let me know if I can provide more clarity.

4

Interesting problem.

Two pathways:

  1. unified calendar with advanced dax

You can stick with a single calendar but introduce a separate lookup table. This table would map each product to its allowed sales dates based on the country. Your measures could then reference this table to filter dates as needed. Alternatively, you could employ a SWITCH statement to dynamically handle the different date logic for each country and product combination.

somethign like:

VAR Country = SELECTEDVALUE('Sales'[Country])
VAR Product = SELECTEDVALUE('Sales'[Product])
VAR QTDResult = 
    SWITCH(
        TRUE(),
        AND(Country = "USA", Product = "A"), CALCULATE([Total Sales], DATESQTD('USACalendar'[Date])),
        AND(Country = "UK", Product = "B"), CALCULATE([Total Sales], DATESQTD('UKCalendar'[Date])),
        CALCULATE([Total Sales], DATESQTD('DefaultCalendar'[Date]))
    )
RETURN 
    QTDResult
  1. multiple calendars with simple dax

In this approach, you’d create individual calendars for each unique set of sales date rules based on the country and product. These calendars would then each be linked to your main sales table. With this setup, your DAX measures could dynamically choose the appropriate calendar based on the current context.