cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Calculation on lignes not appearing on last period

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?
2 REPLIES 2
Community Support

Hi @Myrto

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

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.

Frequent Visitor

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...

Thanks.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors