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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
fatimaarshd
Helper I
Helper I

How to get date one less than date in calendar?

Hi,

 

I have a table which contains Customer ID, Order date and delivery date.

I need to create two visuals. One visual will contain customer ids whose delivery date is one less than date in calendar. And another visual will contain ids whose order date is either one less or current date's date. 

For instance on 3rd Nov, give me all ids whose orders were delivered on 2nd Nov. And give me all ids whose orders were confirmed either on 2nd or 3rd. I have tried this dax but it returns nothing. 

Most Recent Day =
var SummarizeTable = SUMMARIZE('Day after delivery','Day after delivery'[custid],'Day after delivery'[deliverydate],"DayGap",[DayGap])
var FIlterTable = FILTER(SummarizeTable,[DayGap]>0)
var MinMeasure = MINX(FIlterTable,[DayGap])
return
CALCULATE(MAX('Day after delivery'[deliverydate]),FILTER(FIlterTable,[DayGap]=MinMeasure))

 

Edit:

This Dax does give me last day And I can count customer ids against each date. But I don't know how to list those. When I try to list those ids it throws garbage values.

VAR CurrentWeek = MAX('Calendar'[Date])
VAR LastWeek = CurrentWeek - 1 -- Assuming each week is 7 days
RETURN
LastWeek

 

 

Edit2:

I have done this to get one less delivery date but this returns nothing. 

PreviousDeliveryDate =
VAR ChosenDate = SELECTEDVALUE('Calendar'[Date])
VAR PreviousDate = ChosenDate - 1
RETURN
CALCULATE(
MAX('Day after delivery'[deliverydate]),
FILTER('Day after delivery', 'Day after delivery'[Deliverydate] = PreviousDate)
)

 

 

Edit 3:

This Dax works fine but when I add customer id column next to it then all the customer ids are displayed regardless of which date they were delivered:

PreviousDeliveryDate =
VAR ChosenDate = SELECTEDVALUE('Calendar'[Date])
VAR PreviousDate = ChosenDate - 1 VAR LastWeekEndDate = CALCULATE(MAX('Day after delivery'[deliverydate]), ALL('Day after delivery'), 'Day after delivery'[deliverydate] = PreviousDate)
RETURN
LastWeekEndDate

2 REPLIES 2
v-rzhou-msft
Community Support
Community Support

Hi @fatimaarshd ,

 

Due to I don't know your data model, I will give you some suggestions

Please try ALLEXCEPT() function.

PreviousDeliveryDate =
VAR ChosenDate =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR PreviousDate = ChosenDate - 1
VAR LastWeekEndDate =
    CALCULATE (
        MAX ( 'Day after delivery'[deliverydate] ),
        ALLEXCEPT ( 'Day after delivery', 'Day after delivery'[custid] ),
        'Day after delivery'[deliverydate] = PreviousDate
    )
RETURN
    LastWeekEndDate

 

Best Regards,
Rico Zhou

 

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

 

lbendlin
Super User
Super User

You seem to be close to the solution.  Subtracting 1 is an acceptable approach.

 

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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