Dax Count rows todays date and previous day - working days

[cottrera]

Hi I have two tables , a dates table (which diplays working and non working days)and a facts table. I require 2 dax measures please

Measure 1 Count of facts table rows for today - working days only

Measure 2 Count of facts table rows for previous day- working days only

Dates Table
Date	Is working day	Day
25/05/2020	1	Mon
26/05/2020	1	Tue
27/05/2020	1	Wed
28/05/2020	1	Thu
29/05/2020	1	Fri
30/05/2020	0	Sat
31/05/2020	0	Sun
01/06/2020	1	Mon
02/06/2020	1	Tue
03/06/2020	1	Wed
04/06/2020	1	Thu
05/06/2020	1	Fri
06/06/2020	0	Sat
07/06/2020	0	Sun
Facts Table
Date	Trade
29/05/2020	PLUM
29/05/2020	PLUM
29/05/2020	ELEC
29/05/2020	ELEC
29/05/2020	ELEC
29/05/2020	ELEC
29/05/2020	ELEC
29/05/2020	DRAIN
01/06/2020	DRAIN
01/06/2020	DRAIN
01/06/2020	DRAIN
01/06/2020	DRAIN
01/06/2020	DRAIN
01/06/2020	DRAIN

thank you

RIchard

[amitchandak · Accepted solution]

@cottrera , not very clear, this how you can get day wise data

Last Day Non Continous = CALCULATE(sum('order'[Qty]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),Table['Date'])))
or
Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))
or
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
////
This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Or
This Day =sum('order'[Qty])

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[cottrera]

thank you I will check out the links you have sent before continuing with this problem therefore marking this post as resolved

[aj1973]

Hi,

in your date tabe add a column for working days and none working days and use DAX

WorkingDay_Mark =

VAR WeekDayNum =

WEEKDAY ( DimDate[Date] )

RETURN

(

IF ( WeekDayNum = 1 || WeekDayNum = 7 ,0,1)

)

then Count rows that contain 1

Let me know how it works out for t

[cottrera]

Hi 

thank you for your response. I already have this column on my dates table. I am having trouble explaining what I want possibly down to lack of knowledge.

[aj1973]

It's ok,

maybe if you can send us a Dummy Pibx file and your needs we will be more helpfull.

[Greg_Deckler]

@cottrera - Sorry, I am not clear on what you want. What would be your expected results from the information provided and why?

[cottrera]

Hi based on the table below and using 2 dax formulas one for each result I would expect these results

Todays result (01/06/2020) = 6

Previous working day (29/05/2020 = 4

Date	Trade
25/05/2020	PLUM
25/05/2020	DRAIN
25/05/2020	DRAIN
25/05/2020	DRAIN
25/05/2020	DRAIN
25/05/2020	DRAIN
25/05/2020	DRAIN
26/05/2020	ELEC
26/05/2020	ELEC
26/05/2020	ELEC
27/05/2020	ELEC
27/05/2020	ELEC
27/05/2020	ELEC
28/05/2020	PLUM
29/05/2020	PLUM
29/05/2020	PLUM
29/05/2020	PLUM
29/05/2020	PLUM
30/05/2020	PLUM
30/05/2020	PLUM
31/05/2020	PLUM
01/06/2020	PLUM
01/06/2020	ELEC
01/06/2020	ELEC
01/06/2020	DRAIN
01/06/2020	DRAIN
01/06/2020	ELEC