Forum Discussion

alexcatala's avatar
alexcatala
Icon for Helper IV rankHelper IV
1 year ago
Solved

Filter latest Year values blank

Good afternoon,

 

Recently, I was updating my Dashboard and a strange thing happened. I was trying to filter the latest sales for 2025, but all came back blank, all sales, filters by Region, country... but it doesn't happen with previous years, like 2024,2023,2022...

 

We have sales up to 03/May/2025, all regions and countries up to date.

 

I doesn't make sense i could filter the Year 2024 with all the filters and provide me with the total sales per year, but when applying the 2025 filter it brings all blank.

 

I use a DateTable bringing the dates from our Daily Sales.

 

d_DateTable =
VAR EarliestDate =
    MIN ( f_DailySales[Date])
VAR LatestDate =
    MAX ( f_DailySales[Date] )
VAR StartDate =
    DATE ( YEAR ( EarliestDate ), 1, 1 )
VAR EndDate =
    DATE ( YEAR ( LatestDate ), MONTH ( LatestDate ) + 1, 1 ) - 1
RETURN
    ADDCOLUMNS (
        CALENDAR ( StartDate, EndDate ),
        "Year", YEAR ( [Date] ),
        "Quarter", QUARTER ( [Date] ),
        "Month", MONTH ( [Date] ),
        "Week", WEEKNUM ( [Date], 1 ),
        "Year_Quarter", YEAR ( [Date] ) & "_Q"
            & QUARTER ( [Date] ),
        "Year_Month", YEAR ( [Date] ) & "_M"
            & FORMAT ( MONTH ( [Date] ), "00" ),
        "Year_WeekNum", YEAR ( [Date] ) & "_W"
            & FORMAT ( WEEKNUM ( [Date], 1 ), "00" ),
             "Day_Week",WEEKDAY([Date],2),
        "Year_WeekNum (Sk)",
        VAR SkWeek =
            LOOKUPVALUE ( d_Weeks[Week], d_Weeks[Date], [Date] )
        VAR LastSkDay =
            MAX ( d_Weeks[Date] )
        VAR WeekNum_Bias =
            VALUE (
                RIGHT (
                    LOOKUPVALUE ( d_Weeks[Week], d_Weeks[Date], LastSkDay ),
                    1
                )
            )
                - WEEKNUM ( LastSkDay, 1 ) //-1
        VAR AdjustedWeekNum =
            YEAR ( [Date] ) & "_W"
                & FORMAT ( WEEKNUM ( [Date], 1 ) + WeekNum_Bias, "00" )
        RETURN
            IF ( [Date] > LastSkDay, AdjustedWeekNum, SkWeek )
    )
 
This is our Daily Sales formula:
 
Sales (D) =
VAR BoP_Date_Fixed = [BoP Date]
VAR d_StoresAuto_Enriched =
    ADDCOLUMNS (
        d_StoresAuto,
        "Include", MAX (
            [COMP_Active] * ( [OpeningDateAniversary] <= BoP_Date_Fixed ),
            [NEW_Active] * ( [OpeningDateAniversary] > BoP_Date_Fixed )
        )
    )
VAR d_StoresAuto_Enriched_Filtered =
    FILTER ( d_StoresAuto_Enriched, [Include] = 1 )
VAR Result =
    SUMX (
        d_StoresAuto_Enriched_Filtered,
        CALCULATE ( SUM ( f_DailySales[Ext. Price] ) )
    )
RETURN
    IF ( Result = 0, [ND_Sign], Result )
 
This is our StoreAuto formula:
 
d_StoresAuto =
VAR AppendedStores = union(
    SELECTCOLUMNS(d_StoresInput,"Store",d_StoresInput[Store]),
    SELECTCOLUMNS(f_DailySales,"Store",f_DailySales[Store]),
    SELECTCOLUMNS(f_Employees,"Store",f_Employees[Store]),
    SELECTCOLUMNS(f_Payroll,"Store",f_Payroll[Store]),
    SELECTCOLUMNS(filter(f_Recruitment,ISBLANK(f_Recruitment[Store #])=FALSE),"Store",f_Recruitment[Store #]),
    SELECTCOLUMNS(f_DailyMysteryShop,"Store",f_DailyMysteryShop[Store]),
    SELECTCOLUMNS('f_DailyMysteryShop',"Store",'f_DailyMysteryShop'[Store]),
    SELECTCOLUMNS('f_DailyFootfall',"Store",'f_DailyFootfall'[Store])
)
RETURN
ADDCOLUMNS(
 DISTINCT(AppendedStores),
"District", LOOKUPVALUE(d_StoresInput[District],d_StoresInput[Store],[Store]),
"Country",LOOKUPVALUE(d_StoresInput[Country],d_StoresInput[Store],[Store]),
"Region",LOOKUPVALUE(d_StoresInput[Region],d_StoresInput[Store],[Store]),
"StoreType",LOOKUPVALUE(d_StoresInput[StoreType],d_StoresInput[Store],[Store]),
"OpeningDate",LOOKUPVALUE(d_StoresInput[OpeningDate],d_StoresInput[Store],[Store]),
"ClosingDate",LOOKUPVALUE(d_StoresInput[ClosingDate],d_StoresInput[Store],[Store]),
"Comp/New",LOOKUPVALUE(d_StoresInput[Comp/New],d_StoresInput[Store],[Store]),
"StoreName",LOOKUPVALUE(d_StoresInput[StoreName],d_StoresInput[Store],[Store]),
"Lat",LOOKUPVALUE(d_StoresInput[Lat],d_StoresInput[Store],[Store]),
"Long",LOOKUPVALUE(d_StoresInput[Long],d_StoresInput[Store],[Store]),
"Store Code & Name", [Store]& " " & LOOKUPVALUE(d_StoresInput[StoreName],d_StoresInput[Store],[Store]),
"StoreNumber", IFERROR(value([Store]),BLANK())
)
 
Any suggestions on why this is happening?
Need Help
  • v-sgandrathi's avatar
    v-sgandrathi
    1 year ago

    Hi alexcatala,

     

    Thank you for bringing to our attention the issue concerning blank results when applying filters for the year 2025 in your sales dashboard. After a thorough review of your DAX logic and the underlying data model structure, we have identified a probable root cause contributing to this unexpected behavior.

    To validate the output of the [BoP Date] measure, we recommend adding a card visual to your report to display its current value. Once this visual is in place, apply a filter for the year 2025 and observe the result. If the card returns a blank value or displays a date that does not fall within the 2025 calendar year, this would confirm that [BoP Date] is not resolving correctly within the applied filter context and is likely the root cause of the blank sales data.

    To address this, we suggest implementing a fallback mechanism within your DAX logic to ensure [BoP Date] does not return a blank.

    This approach ensures that in the absence of a valid [BoP Date], the calculation will default to the current date, allowing the measure to continue functioning as expected.

    Please ensure that there is an active one-to-many relationship between d_DateTable[Date] and f_DailySales[Date]. We also recommend temporarily removing any filters on Region, Country, or StoreType to isolate and further validate the behavior.

     

    If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

     

    Thank you.

4 Replies

  • v-sgandrathi's avatar
    v-sgandrathi
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi alexcatala,

     

    Thank you for bringing to our attention the issue concerning blank results when applying filters for the year 2025 in your sales dashboard. After a thorough review of your DAX logic and the underlying data model structure, we have identified a probable root cause contributing to this unexpected behavior.

    To validate the output of the [BoP Date] measure, we recommend adding a card visual to your report to display its current value. Once this visual is in place, apply a filter for the year 2025 and observe the result. If the card returns a blank value or displays a date that does not fall within the 2025 calendar year, this would confirm that [BoP Date] is not resolving correctly within the applied filter context and is likely the root cause of the blank sales data.

    To address this, we suggest implementing a fallback mechanism within your DAX logic to ensure [BoP Date] does not return a blank.

    This approach ensures that in the absence of a valid [BoP Date], the calculation will default to the current date, allowing the measure to continue functioning as expected.

    Please ensure that there is an active one-to-many relationship between d_DateTable[Date] and f_DailySales[Date]. We also recommend temporarily removing any filters on Region, Country, or StoreType to isolate and further validate the behavior.

     

    If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

     

    Thank you.

    • v-sgandrathi's avatar
      v-sgandrathi
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi alexcatala,

       

      We haven’t heard from you on the last response and was just checking back to see if your query was answered.
      Otherwise, will respond back with the more details and we will try to help .

      If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

       

      Thank you.

      • v-sgandrathi's avatar
        v-sgandrathi
        Icon for Community Support rankCommunity Support
        1 year ago

        Hi alexcatala,

         

        As we have not received a response from you yet, I would like to confirm whether you have successfully resolved the issue or if you require further assistance.

        If the issue has been resolved, please mark the helpful reply as a "solution" to indicate that the question has been answered and to assist others in the community.

        Thank you for your cooperation. Have a great day.