Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Myrto
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
v-zhengdxu-msft
Community Support
Community Support

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.

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:

Myrto_0-1708967595726.png

 

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. 

 

CustomerMonthsWeek1st Day of WeekSum of Shipment Count
Fjanv422/01/20244906
Jjanv422/01/2024369
Rjanv422/01/20245488
Ijanv422/01/20241118
Hjanv422/01/20243660
Cjanv422/01/20244245
Ajanv422/01/2024745
Djanv422/01/202414346
Kjanv422/01/20241101
Pjanv422/01/2024452
Mjanv422/01/2024935
Ljanv422/01/20244546
Gjanv422/01/2024523
Tjanv422/01/20241769
Sjanv422/01/20242570
Ojanv422/01/20242721
Bjanv422/01/20242314
Njanv422/01/20241031
Fjanv529/01/20244698
Jjanv529/01/2024466
Rjanv529/01/20246839
Ijanv529/01/2024879
Hjanv529/01/20243397
Cjanv529/01/20242397
Ajanv529/01/2024783
Djanv529/01/202410047
Kjanv529/01/2024945
Pjanv529/01/2024427
Mjanv529/01/20241301
Ljanv529/01/20244605
Gjanv529/01/2024521
Tjanv529/01/20241689
Sjanv529/01/20242455
Ojanv529/01/20243072
Bjanv529/01/20242244
Njanv529/01/20241121
Ffévr605/02/20244726
Jfévr605/02/2024437
Rfévr605/02/20246050
Ifévr605/02/20241163
Hfévr605/02/20243606
Cfévr605/02/20242192
Afévr605/02/2024903
Dfévr605/02/202410212
Kfévr605/02/2024726
Pfévr605/02/2024512
Mfévr605/02/20241130
Lfévr605/02/20244448
Gfévr605/02/2024521
Tfévr605/02/20241718
Sfévr605/02/20242696
Ofévr605/02/20242933
Bfévr605/02/20242377
Nfévr605/02/20241242
Ffévr712/02/20241164
Jfévr712/02/202469
Rfévr712/02/202420
Ifévr712/02/202420
Hfévr712/02/20242
Cfévr712/02/20241
Afévr712/02/20243
Kfévr712/02/20244
Pfévr712/02/20241
Lfévr712/02/20241
Gfévr712/02/20242
Tfévr712/02/20241

 

What I want to see is this:

Myrto_1-1708967952756.png

 

 

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.