Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All
I was trying something but failing to understand the logic.
I have one date table and another master table which has the data. Connected with date columns (1 to many)
I want to do calculations based on day name. i.e. if its Monday, I want to take a count or a sum for last three days excluding Saturday and Sunday. which will be Wed +Thurs + Fri.
If it is tuesday then sum = Thurs + Fri + Mon
and so on.
Any logic that I can use here or more information if you all need?
Solved! Go to Solution.
Hi @eshanpacheriwal ,
I created some data:
Main Table:
Date:
Here are the steps you can follow:
1. Create calculated column.
weekday = WEEKDAY('MainTable'[Date],2)
workday =
IF(
'MainTable'[weekday] in {6,7},0,1)
Index =
RANKX(FILTER(MainTable,'MainTable'[workday]=1),[Date],,ASC,Dense)
Sum_3day = CALCULATE(SUM('MainTable'[amount]),FILTER(ALL(MainTable),'MainTable'[Index]>=EARLIER(MainTable[Index])+2&&'MainTable'[Index]<=EARLIER(MainTable[Index])+4))
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @eshanpacheriwal ,
I created some data:
Main Table:
Date:
Here are the steps you can follow:
1. Create calculated column.
weekday = WEEKDAY('MainTable'[Date],2)
workday =
IF(
'MainTable'[weekday] in {6,7},0,1)
Index =
RANKX(FILTER(MainTable,'MainTable'[workday]=1),[Date],,ASC,Dense)
Sum_3day = CALCULATE(SUM('MainTable'[amount]),FILTER(ALL(MainTable),'MainTable'[Index]>=EARLIER(MainTable[Index])+2&&'MainTable'[Index]<=EARLIER(MainTable[Index])+4))
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
@eshanpacheriwal Right, so it is similar to Net Work Days: https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109
Basically something like:
Measure =
VAR __Date = MAX('Table'[Date])
VAR __Calendar =
TOPN(
3,
FILTER(
ADDCOLUMNS(CALENDAR(__Date - 4,__Date-1),"__WeekDay",WEEKDAY(__Date,2)),
[__WeekDay]<6
)
)
RETURN
SUMX(FILTER('Table',[Date] IN __Calendar),[Value])
@Greg_Deckler
Thanks for such a quick response. Will you be able to explain me the logic?
I tried the following but may be I am using it wrong as it throws error:
The number of arguments is invalid. Function CONTAINSROW must have a value for each column in the table expression.
@eshanpacheriwal My bad, I almost got it right without testing see below. Basically you create a quick calendar table with dates -4 and -1 from whatever the date is within context. You add a column to determine the weekday of each row of dates in the calendar table, you filterout weekends, grab the 3 latest dates and then you need the SELECTCOLUMNS to get it down to a single column (because we had to add one). Then you can use a FILTER with an IN to filter your table down to just those dates. That's the essence of it.
Measure =
VAR __Date = MAX('Table'[Date])
VAR __Calendar =
SELECTCOLUMNS(
TOPN(
3,
FILTER(
ADDCOLUMNS(CALENDAR(__Date - 4,__Date-1),"__WeekDay",WEEKDAY(__Date,2)),
[__WeekDay]<6
)
),
"Date",[Date]
)
RETURN
SUMX(FILTER('Table',[Date] IN __Calendar),[Value])
@Greg_Deckler
I think I might be understanding the logic wrong.
It is giving me the count of all the accounts till date instead of just the last three now.
@eshanpacheriwal May have to add the optional parameter to TOPN to change the sort direction.
User | Count |
---|---|
88 | |
74 | |
69 | |
65 | |
58 |
User | Count |
---|---|
104 | |
94 | |
76 | |
62 | |
59 |