Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Al ,
I have a Yesterday SHipment measure as follows:
But Now I want to add a logic to it that ,
"if today is Monday then add the shipment of Friday +Saturday + Sunday as previuos day shipment; else previous day shipment"
Date | Tons | Yesterday Shipment |
Thursday, April 1, 2021 | 100 | 0 |
Friday, April 2, 2021 | 150 | 100 |
Saturday, April 3, 2021 | 200 | 150 |
Sunday, April 4, 2021 | 250 | 200 |
Monday, April 5, 2021 | 300 | 600 (= 150+200+250) |
Tuesday, April 6, 2021 | 350 | 300 |
Wednesday, April 7, 2021 | 400 | 350 |
Thursday, April 8, 2021 | 450 | 400 |
Friday, April 9, 2021 | 500 | 450 |
Saturday, April 10, 2021 | 550 | 500 |
Sunday, April 11, 2021 | 600 | 550 |
Monday, April 12, 2021 | 650 | 1650 (= 500+550+600) |
Tuesday, April 13, 2021 | 700 | 650 |
Wednesday, April 14, 2021 | 750 | 700 |
Thursday, April 15, 2021 | 800 | 750 |
Friday, April 16, 2021 | 850 | 800 |
Saturday, April 17, 2021 | 900 | 850 |
Sunday, April 18, 2021 | 950 | 900 |
Monday, April 19, 2021 | 1000 | 2700 |
Tuesday, April 20, 2021 | 1050 | 1000 |
Wednesday, April 21, 2021 | 1100 | 1050 |
Thursday, April 22, 2021 | 1150 | 1100 |
Friday, April 23, 2021 | 1200 | 1150 |
Saturday, April 24, 2021 | 1250 | 1200 |
Sunday, April 25, 2021 | 1300 | 1250 |
Monday, April 26, 2021 | 1350 | 3750 |
Solved! Go to Solution.
varWeekday will return "Monday" when the visible date is Monday. It isn't based on the system date - not TODAY().
Is that what you are asking?
So if you load data and it says Jan 19, 2015 for a given record, the measure I gave you will go "Oh, that is a monday, so do 3 days of totals". If the next row is Jan 20, 2015, it will go "Nope, this is Tuesday, so just look back one day."
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingAll 3 solution by DAX formula, calculated column and PQ.
Yesterday_DAX =
VAR __dt = MAX( SHIPMENT[Date] )
RETURN
IF(
WEEKDAY( __dt, 2 ) = 1,
CALCULATE( [total], DATESINPERIOD( SHIPMENT[Date], __dt - 1, -3, DAY ) ),
CALCULATE( [total], DATEADD( SHIPMENT[Date], -1, DAY ) )
)
Yesterday_CC =
VAR __dt = SHIPMENT[Date]
RETURN
IF(
WEEKDAY( SHIPMENT[Date], 2 ) = 1,
SUMX(
FILTER( SHIPMENT, __dt - 3 <= SHIPMENT[Date] && SHIPMENT[Date] <= __dt - 1 ),
SHIPMENT[Tons]
),
SUMX( FILTER( SHIPMENT, SHIPMENT[Date] = __dt - 1 ), SHIPMENT[Tons] )
)
PQ solution, unnecessarily overcomplicated, but just for fun,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZIxD4IwFIT/CunM0HtQoKOLmxMkDoSBBBJJjBq0g//eGLHvaV7nu156972+N90prPdpfObZ7rYu5wx5RpZgcgNrzZD3Zr8uQifW3Udvx0dYhaOIDtoS2nAResn6lnC4St1Fvdjed2GWX6zYsAUc5+nyY6mjpfxm/PVs2OG0nj7qzuo9YdnitKLgLSurNQWPWTm1KnjM2ia6ggetnV4WvGmjUgVv2iSwgjf1KlfwpF4FCy9vS+1LVlhScEncKBJ4SdwpVMDE04ISiKkUHpUx8bIoVMhUCcc7Y3gB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Tons = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Tons", Int64.Type}}),
Custom1 = Table.AddColumn(
#"Changed Type",
"Yesterday_PQ",
let l=Table.ToColumns(#"Changed Type"), rs=Record.FromList(l{1}, List.Transform(l{0}, each Text.From(Number.From(_))))
in each if Date.DayOfWeek([Date])=1
then List.Accumulate({Number.From([Date])-3 .. Number.From([Date])-1}, 0, (s,c) => s+Record.FieldOrDefault(rs, Text.From(c), 0))
else Record.FieldOrDefault(rs, Text.From(Number.From([Date]-#duration(1,0,0,0))))
)
in
Custom1
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@SH-VE
If you want to add a column to your table then add a column with this code. Hope you have data for all days.
Yesterday Tons =
VAR __DATE = Table1[Date]
RETURN
IF(
WEEKDAY([Date]) = 2,
CALCULATE(SUM(Table1[Tons]), Table1[Date] < __DATE && Table1[Date] >= __DATE-3 , ALL(Table1)),
CALCULATE(SUM(Table1[Tons]), Table1[Date] = __DATE -1, ALL(Table1))
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
See if this works for you @SH-VE
I used a date table here. Made it really simple to walk back the days.
This is the measure I used:
Yesterday's Tons =
VAR varWeekDay = MAX('Date'[Day Name])
VAR varCurrentDay = MAX('Date'[Date])
VAR varPreviousDayTons =
CALCULATE(
[Total Tons],
'Date'[Date] = varCurrentDay - 1
)
VAR varWeekendTons =
CALCULATE(
[Total Tons],
DATESBETWEEN(
'Date'[Date],
varCurrentDay - 3,
varCurrentDay -1
)
)
VAR Result =
IF(
varWeekDay = "Monday",
varWeekendTons,
varPreviousDayTons
)
RETURN
Result
It has the definition for both weekend tons (Fri-Sun) and regular tons (just yesterday) but it will only calculate the one it needs based on if the weekday is Monday - which comes from the date table.
Here is my PBIX file if you want to see how I set it all up. You'll notice used a filter in CALCULATE rather than filtering the FACT table based on the date. Generally considered good practice. Filtering an entire table shoudl be avoided. The larger the model, the worse the performance impact becomes.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCan you share the logic for "Day name " and using MAX for it.
For e.g. For today this expresssion shows Wednesday. Should it be showing "Monday". In my actual report it did not add Fri+SAt+Sun for today.
This Is how I used in my report:
@SH-VE the day name is a field in my date table - which was in the PBIX file I used. MAX() is just a way to turn the currently visible day name into a scalar value that I can use in a filter later.
Is that what you are asking?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYes , Thats what my question was .
However, I was wondering in what case will "Var Weekday" will give the result as 'Monday' which will then kick-in the "If (var Weekday = "monday") part of Dax. Hope I am understanding it correct.
varWeekday will return "Monday" when the visible date is Monday. It isn't based on the system date - not TODAY().
Is that what you are asking?
So if you load data and it says Jan 19, 2015 for a given record, the measure I gave you will go "Oh, that is a monday, so do 3 days of totals". If the next row is Jan 20, 2015, it will go "Nope, this is Tuesday, so just look back one day."
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHI @edhans . Thanks for your response.
I think I did not ask this question on this forum in correct context. Apologies for that.
Basically, I plan to use this query as part of a table visual . Please see below the visual . Here 'yesterday tons' right now is simply :
I am not sure what is it that I am doing incorrect .
I have uploaded a sample data in excel from this table (basically only the columns which are used here) one-drive. Here is the link https://1drv.ms/x/s!AoEAHRm368Nsbf4XFy6h4yJ4J9s?e=QeTq1d
Please let me know what you think about it.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
44 | |
28 | |
22 | |
11 | |
8 |
User | Count |
---|---|
74 | |
51 | |
45 | |
16 | |
12 |