cancel
Showing results for
Did you mean:
Helper IV

## Previous working day Total

I am trying to get a total for the previous working day total in my model

My data is structured as below

 PO No Purch Date Quantity 100008 30-Sep-21 40 100010 1-Oct-21 15 100011 4-Oct-21 20 100100 15-Oct-21 20 100104 15-Oct-21 50 100101 18-Oct-21 125 100103 18-Oct-21 25

The total for previous day should consider that

1. if it is first day of month, previous Qty should show the total as the same as the current Qty

2. For Monday, previous working day should be showing Friday qty

Expected result is

 Purch Date Total Qty Previous qty 30-Sep-21 40 0 1-Oct-21 15 15 4-Oct-21 20 15 15-Oct-21 70 0 18-Oct-21 150 70

Tried using the measure

Previous Work Day Qty =
var MaxDate = MAXX(FILTER(ALL('Calendar'), 'Calendar'[Date] < SELECTEDVALUE(Calendar[Date])), Calendar[Date])
return
CALCULATE(
SUM('Purch Fact'[Quantity]), FILTER(ALL('Calendar'), Calendar[Date] = MaxDate)
)
1 ACCEPTED SOLUTION
Community Support

Hi @Pbi07 ,

I updated your sample pbix file(see attachment), please check whether that is what you want.

``````Previous Work Day Qty =
VAR _curdate =
SELECTEDVALUE ( 'Purch Fact'[Purch Date] )
VAR _preworkdate =
CALCULATE (
MAX ( 'Calendar'[Date] ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Date] < _curdate
&& WEEKDAY ( 'Calendar'[Date], 2 ) <= 5
)
)
RETURN
IF (
DAY ( _curdate ) = 1,
[Total Qty],
SUMX (
FILTER ( ALLSELECTED ( 'Purch Fact' ), 'Purch Fact'[Purch Date] = _preworkdate ),
[Total Qty]
)
) + 0``````

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Community Support

Hi @Pbi07 ,

I updated your sample pbix file(see attachment), please check whether that is what you want.

``````Previous Work Day Qty =
VAR _curdate =
SELECTEDVALUE ( 'Purch Fact'[Purch Date] )
VAR _preworkdate =
CALCULATE (
MAX ( 'Calendar'[Date] ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Date] < _curdate
&& WEEKDAY ( 'Calendar'[Date], 2 ) <= 5
)
)
RETURN
IF (
DAY ( _curdate ) = 1,
[Total Qty],
SUMX (
FILTER ( ALLSELECTED ( 'Purch Fact' ), 'Purch Fact'[Purch Date] = _preworkdate ),
[Total Qty]
)
) + 0``````

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

Hi,

Please check the below picture and the attached pbix file.

I created a calculated column in your calendar table in order to help define weekdays.

Previous Work Day Qty =
VAR currentdate =
MAX ( 'Calendar'[Date] )
VAR previousworkingdate =
MAXX (
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Date] < currentdate
&& NOT ( 'Calendar'[Weekdayname CC] IN { "Saturday", "Sunday" } )
),
'Calendar'[Date]
)
RETURN
IF (
HASONEVALUE ( 'Calendar'[Date] ),
IF (
NOT ( ISBLANK ( [Total Qty] ) ),
SWITCH (
TRUE (),
SELECTEDVALUE ( 'Calendar'[Date] ) = STARTOFMONTH ( 'Calendar'[Date] ), [Total Qty],
CALCULATE ( [Total Qty], 'Calendar'[Date] = previousworkingdate )
) + 0
)
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Helper IV

Thanks @Jihwan_Kim

I can see it working in that model.

Trying to understand how this is working. When i try to bring this into my model, my total is showing up as 0, eventhough there is value. WIll you be able to explain that expression in the return parameter please.

Also, is that HASONEVALUE being false returning that 0 for my expression. Bit confused with it.

Super User

Hi,

Sorry to say that it is quite difficult to fully understand your question without seeing the model and visualization.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Helper IV

Thanks @Jihwan_Kim . Let me see if i can take a sample of that model for you.

Just curious what is the significance of that HASONEVALUE in the measure. Looks to me that it is causing the issue in my measure. Trying to see if i can avoid and use it.

Super User

@Pbi07 , You can try like these examples

This Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('Table'[Qty]), previousday('Date'[Date]))

Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Announcements

#### Check it Out!

Mark your calendars and join us on Thursday, August 25 at 11a PDT for a great session with Ted Pattison!

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.