Getting value for previous days on a specific day doesn't return data even if there's data

[julsr]

Hello everyone,

I have a formula that looks for the previous days (for example if today is Tuesday, it'll return as a result the previous 6 Mondays). It was working without problem but now when I filter for yesterday (04/11/2024), it returns null for all the previous Mondays even if I have data in my database (apparently is only happening with yesterday). Does anyone know what's wrong?

This is the code:

[Measure_Name] =
VAR CurrentDate = MAX(Table_Name[Date_Column])
-- Calculate dates for the previous same weekdays
VAR Previous1 = CurrentDate - 7
VAR Previous2 = CurrentDate - 14
VAR Previous3 = CurrentDate - 21
VAR Previous4 = CurrentDate - 28
VAR Previous5 = CurrentDate - 35
VAR Previous6 = CurrentDate - 42
-- Summarize and sum the values for the previous same weekdays
VAR Summed1 =
CALCULATE(
[Base_Measure],
Table_Name[Date_Column] = Previous1
)
VAR Summed2 =
CALCULATE(
[Base_Measure],
Table_Name[Date_Column] = Previous2
)
VAR Summed3 =
CALCULATE(
[Base_Measure],
Table_Name[Date_Column] = Previous3
)
VAR Summed4 =
CALCULATE(
[Base_Measure],
Table_Name[Date_Column] = Previous4
)
VAR Summed5 =
CALCULATE(
[Base_Measure],
Table_Name[Date_Column] = Previous5
)
VAR Summed6 =
CALCULATE(
[Base_Measure],
Table_Name[Date_Column] = Previous6
)
RETURN
"Dates: " &
FORMAT(Previous1, "mm/dd/yyyy") & " (" & FORMAT(Summed1, "#,##0.00") & "), " &
FORMAT(Previous2, "mm/dd/yyyy") & " (" & FORMAT(Summed2, "#,##0.00") & "), " &
FORMAT(Previous3, "mm/dd/yyyy") & " (" & FORMAT(Summed3, "#,##0.00") & "), " &
FORMAT(Previous4, "mm/dd/yyyy") & " (" & FORMAT(Summed4, "#,##0.00") & "), " &
FORMAT(Previous5, "mm/dd/yyyy") & " (" & FORMAT(Summed5, "#,##0.00") & "), " &
FORMAT(Previous6, "mm/dd/yyyy") & " (" & FORMAT(Summed6, "#,##0.00") & ")"

Thank you everyone!

[Anonymous]

Hi @julsr ,

Without knowing the exact content of [Base_Measure] in your expression, it's hard to tell exactly where the error is occurring.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[julsr]

Hi! Thanks for your reply. The [base_measure] is just a sum with filters that is working for all the other days (except 04/11/2024), this is the unique day where the problem is showing (at least known). I have data available for yesterday (04/11/2024) and for the previous 6 Mondays but as you can see in the first image, it looks like there's no data in there.

BASE_MEASURE =

CALCULATE(

    SUM(TABLE_NAME[BASE]),

    FILTER(

        TABLE_NAME,

        TABLE_NAME[CODE] = "A01" ||

        TABLE_NAME[CODE]="A03")

)

[Anonymous]

Hi @julsr ,

As per your description, I created some sample data and put in the two measures you provided. when I select Nov 4 in the slicer, it returns the results correctly.

So, please check your raw data if these dates match the filter TABLE_NAME[CODE] = “A01” | | TABLE_NAME[CODE] = “A03” and if not, it will return blank.

If the problem persists, you can also check the differences between these sample data and yours, adding more details so we can better help you solve the problem.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[julsr · Accepted solution]

Thanks for your help! 

I've found the error. It was related to the time change and how it was stored in the database. You can see that the time is attached to the date. I modified the formula to ensure we are not considering the time on it, and it worked!

[Measure_Name] =
VAR CurrentDate = DATE(YEAR(MAX(Table_Name[Date_Column])),
MONTH(MAX(Table_Name[Date_Column])),
DAY(MAX(Table_Name[Date_Column])))
-- Calculate dates for the previous same weekdays
VAR Previous1 = DATE(YEAR(CurrentDate - 7),
MONTH(CurrentDate - 7),
DAY(CurrentDate - 7))
VAR Previous2 = DATE(YEAR(CurrentDate - 14),
MONTH(CurrentDate - 14),
DAY(CurrentDate - 14))
-- Summarize and sum the values for the previous same weekdays
VAR Summed1 =
CALCULATE(
SUM([Value_Measure]),
FILTER(
ALL(Table_Name),
DATE(YEAR(Table_Name[Date_Column]),
MONTH(Table_Name[Date_Column]),
DAY(Table_Name[Date_Column])) = Previous1
)
)
VAR Summed2 =
CALCULATE(
SUM([Value_Measure]),
FILTER(
ALL(Table_Name),
DATE(YEAR(Table_Name[Date_Column]),
MONTH(Table_Name[Date_Column]),
DAY(Table_Name[Date_Column])) = Previous2
)
)
-- Calculate total and average
VAR Total = Summed1 + Summed2
RETURN
Total/2

[ajohnso2]

Try this

[Measure_Name] =
VAR CurrentDate = MAX(Table_Name[Date_Column])
-- Calculate dates for the previous same weekdays
VAR Previous1 = CurrentDate - 7
VAR Previous2 = CurrentDate - 14
VAR Previous3 = CurrentDate - 21
VAR Previous4 = CurrentDate - 28
VAR Previous5 = CurrentDate - 35
VAR Previous6 = CurrentDate - 42
-- Summarize and sum the values for the previous same weekdays
VAR Summed1 =
CALCULATE(
[Base_Measure],
ALL(Table_Name[Date_Column]) = Previous1
)
VAR Summed2 =
CALCULATE(
[Base_Measure],
ALL(Table_Name[Date_Column]) = Previous2
)
VAR Summed3 =
CALCULATE(
[Base_Measure],
ALL(Table_Name[Date_Column]) = Previous3
)
VAR Summed4 =
CALCULATE(
[Base_Measure],
ALL(Table_Name[Date_Column]) = Previous4
)
VAR Summed5 =
CALCULATE(
[Base_Measure],
ALL(Table_Name[Date_Column]) = Previous5
)
VAR Summed6 =
CALCULATE(
[Base_Measure],
ALL(Table_Name[Date_Column]) = Previous6
)
RETURN
"Dates: " &
FORMAT(Previous1, "mm/dd/yyyy") & " (" & FORMAT(Summed1, "#,##0.00") & "), " &
FORMAT(Previous2, "mm/dd/yyyy") & " (" & FORMAT(Summed2, "#,##0.00") & "), " &
FORMAT(Previous3, "mm/dd/yyyy") & " (" & FORMAT(Summed3, "#,##0.00") & "), " &
FORMAT(Previous4, "mm/dd/yyyy") & " (" & FORMAT(Summed4, "#,##0.00") & "), " &
FORMAT(Previous5, "mm/dd/yyyy") & " (" & FORMAT(Summed5, "#,##0.00") & "), " &
FORMAT(Previous6, "mm/dd/yyyy") & " (" & FORMAT(Summed6, "#,##0.00") & ")"

[julsr]

Thanks for your reply, I got this result

I did it like this that is the right way to use the ALL but still didn't work

ALL(Table_Name,Table_Name[Date_Column]) = Previous1