Forum Discussion

garythomannCoGC's avatar
garythomannCoGC
Impactful Individual
2 years ago
Solved

DAX CALENDARAUTO function returns empty table when used in Power BI Desktop

Doh, followed along with the calendarauto example from the video (sqlbi.com) but no dates produced. No syntax errors. Using 07.40 - Moving average.pbix Table of Dates = CALENDARAUTO() works ...
  • FreemanZ's avatar
    2 years ago

    hi garythomannCoGC ,

     

    try like:

    Dates (CALENDARAUTO) =
        VAR _FirstDate_CustomerBirth = MIN ( 'Customer'[Birth Date] )
        VAR _FirstDate_ProductAvailable = MIN ( 'Product'[Available Date] )
        VAR _FirstDate_SalesDelivery = MIN ( 'Sales'[Delivery Date] )
        VAR _FirstDate_SalesOrder = MIN ( 'Sales'[Order Date] )
        VAR _FirstDate =
            IF ( _FirstDate_SalesOrder < _FirstDate_SalesDelivery, _FirstDate_SalesOrder, _FirstDate_SalesDelivery )
        VAR _DateTable =
             FILTER (
                 CALENDARAUTO(),
                 YEAR ( [Date] ) >= YEAR(_FirstDate) -- 'CALENDARAUTO'.[Date]
        )
    RETURN
        ADDCOLUMNS(
            _DateTable,
            "Year", YEAR ( [Date] ),
            "Month", FORMAT ( [Date], "mmm" ),
            "Month Number", MONTH ( [Date] ),
            "Quarter", FORMAT ( [Date], "\QQ yyyy" )
        )