Calculation on lignes not appearing on last period

[Myrto]

Hi All,

I'm facing a weird issue by comparing the total shipments of my customers over the last 4 weeks. I have a customer ho didn't ship anything on Week 4 but he did the previous 3 weeks. I have created two measures, one extracting the latest week available in my database and another measure to calculate the total shipments filtered on this latest week:

Last Given Date Volumes Hebdo = MAX(Volumes_Clients_Hebdo[1st Day of Week])

Total Shipments Current Week = CALCULATE(

    [TOT Shipments],

    DATESBETWEEN(DIM_DAY[DATE_REF],

    DATE(YEAR([Last Given Date Volumes Hebdo]),MONTH([Last Given Date Volumes Hebdo]),DAY([Last Given Date Volumes Hebdo])),

    DATE(YEAR([Last Given Date Volumes Hebdo]),MONTH([Last Given Date Volumes Hebdo]),DAY([Last Given Date Volumes Hebdo])+6)

    )

)

The first measure is giving me the right date. The second measure works fine whenever a customer is present on all previous weeks, but for the one who is not excisting on Week 4, instead of showing me 0 (which is what I want) the calculation is giving me the total shipments of the Week 3, which is actually the latest week where I have his data. But weirdest thing is that if I code the datesbetween function with hardcoded dates, it works!

CALCULATE(

    [TOT Shipments],

    DATESBETWEEN(DIM_DAY[DATE_REF],

    DATE(2024,2,12),

    DATE(2024,2,18)

    )

)

What am I doing wrong? 

[v-zhengdxu-msft]

Hi @Myrto 

I 've known your issue:

If code the datesbetween function with hardcoded dates, the function works. 

Maybe you can check if the field [Last Given Date Volumes Hebdo] is blank?

If the above one can't help you, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Myrto]

Hi @v-zhengdxu-msft ,

thanks for taking the time to get into my case. To answer you question, the field  [Last Given Date Volumes Hebdo] isn't blank, but is searching for the latest date in my fact table column [1st Day of Week] formatted as date type, which is 12/02/2024:

To get this date, I simply coded = LASTDATE(Volumes_Clients_Hebdo[1st Day of Week]).

For the context, I have two tables, a fact one with shipments by customer over the last three weeks where the given date is the first day of each week, and I have a dim_date table regularly marked as my date table, where my [date_ref] column is the reference date formatted in date type, without timestamp. I have linked these two tables by the columns [date_ref] as 1 to column column [1st Day of Week] as many. 

Customer	Months	Week	1st Day of Week	Sum of Shipment Count
F	janv	4	22/01/2024	4906
J	janv	4	22/01/2024	369
R	janv	4	22/01/2024	5488
I	janv	4	22/01/2024	1118
H	janv	4	22/01/2024	3660
C	janv	4	22/01/2024	4245
A	janv	4	22/01/2024	745
D	janv	4	22/01/2024	14346
K	janv	4	22/01/2024	1101
P	janv	4	22/01/2024	452
M	janv	4	22/01/2024	935
L	janv	4	22/01/2024	4546
G	janv	4	22/01/2024	523
T	janv	4	22/01/2024	1769
S	janv	4	22/01/2024	2570
O	janv	4	22/01/2024	2721
B	janv	4	22/01/2024	2314
N	janv	4	22/01/2024	1031
F	janv	5	29/01/2024	4698
J	janv	5	29/01/2024	466
R	janv	5	29/01/2024	6839
I	janv	5	29/01/2024	879
H	janv	5	29/01/2024	3397
C	janv	5	29/01/2024	2397
A	janv	5	29/01/2024	783
D	janv	5	29/01/2024	10047
K	janv	5	29/01/2024	945
P	janv	5	29/01/2024	427
M	janv	5	29/01/2024	1301
L	janv	5	29/01/2024	4605
G	janv	5	29/01/2024	521
T	janv	5	29/01/2024	1689
S	janv	5	29/01/2024	2455
O	janv	5	29/01/2024	3072
B	janv	5	29/01/2024	2244
N	janv	5	29/01/2024	1121
F	févr	6	05/02/2024	4726
J	févr	6	05/02/2024	437
R	févr	6	05/02/2024	6050
I	févr	6	05/02/2024	1163
H	févr	6	05/02/2024	3606
C	févr	6	05/02/2024	2192
A	févr	6	05/02/2024	903
D	févr	6	05/02/2024	10212
K	févr	6	05/02/2024	726
P	févr	6	05/02/2024	512
M	févr	6	05/02/2024	1130
L	févr	6	05/02/2024	4448
G	févr	6	05/02/2024	521
T	févr	6	05/02/2024	1718
S	févr	6	05/02/2024	2696
O	févr	6	05/02/2024	2933
B	févr	6	05/02/2024	2377
N	févr	6	05/02/2024	1242
F	févr	7	12/02/2024	1164
J	févr	7	12/02/2024	69
R	févr	7	12/02/2024	20
I	févr	7	12/02/2024	20
H	févr	7	12/02/2024	2
C	févr	7	12/02/2024	1
A	févr	7	12/02/2024	3
K	févr	7	12/02/2024	4
P	févr	7	12/02/2024	1
L	févr	7	12/02/2024	1
G	févr	7	12/02/2024	2
T	févr	7	12/02/2024	1

What I want to see is this:

where customer B,D,M,NO and S have nothing (or 0) on week 7. This will help me to make a measure to pull up my lost customers. To do so, I have created the following measure for the last week :

Total Shipments par Jour Current Week = CALCULATE(

    [Sum of shipments count],

    FILTER(Volumes_Clients_Hebdo,

    Volumes_Clients_Hebdo[1st Day of Week] = LASTDATE(Volumes_Clients_Hebdo[1st Day of Week])

    )

)

I tried to write this measure in many different ways, either using function datesbetween, or lastdate and similar ones, but the result is always the same.. for the customers who have no shipments on week 7, the result is giving me the lastnoblank, where instead I want to see 0 (or empty). Same measure with hardcoded date as Date(2024,02,12) works perfectly... 

Please help me find out cause I've been stuck on this topic since long 😞 

Thanks.