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.
Date(m/d/year) Order id
1/5/2020 1
1/6/2020 2
1/7/2020 3
2/6/2020 1
2/6/2020 2
2/6/2020 4
3/6/2020 3
3/6/2020 5
3/6/2020 6
when I select the Feb I should get this output
Date(m/d/year) Order id Flag
2/6/2020 1 1
2/6/2020 2 1
2/6/2020 4 0
when I select the March I should get this output
Date(m/d/year) Order id Flag
3/6/2020 3 1
3/6/2020 5 0
3/6/2020 6 0
the output will be "1" if the order id is present in the previous month. Thank you
Solved! Go to Solution.
The measure I suggested above should work. If you want a calculated column:
Flag =
VAR currentOrder_ = Table1[Order id]
VAR limit_ = EOMONTH ( Table1[Date(m/d/year) ] , -1 )
RETURN
CALCULATE (
COUNT ( Table1[Order id] ),
Table1[Order id] = currentOrder_,
Table1[Date(m/d/year) ] <= limit_,
ALL ( Table1 )
) + 0
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
The measure I suggested above should work. If you want a calculated column:
Flag =
VAR currentOrder_ = Table1[Order id]
VAR limit_ = EOMONTH ( Table1[Date(m/d/year) ] , -1 )
RETURN
CALCULATE (
COUNT ( Table1[Order id] ),
Table1[Order id] = currentOrder_,
Table1[Date(m/d/year) ] <= limit_,
ALL ( Table1 )
) + 0
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
i wanted calculated column
Hi @qwaiti27231
Previous month or previous monthS?? Your talk about the former but your example shows the latter. For the latter option:
Flag =
VAR currentOrder_ =
SELECTEDVALUE ( Table1[Order id] )
VAR limit_ =
EOMONTH ( SELECTEDVALUE ( Table1[Date] ), -1 )
RETURN
CALCULATE (
COUNT ( Table1[Order id] ),
Table1[Order id] = currentOder_,
Table1[Date] <= limit_,
ALL ( Table1 )
)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@qwaiti27231 , replies are nested, check my last replay with file. Two measures added into the file
@qwaiti27231 , Try a new measure like
if(isblank(countx(filter(all(Table), table[date] > eomonth(max(Table[date]),-2) && table[date] <= eomonth(max(Table[date]),-1) && table[Order id] =max(Table[Order id])),[Order Id])),0,1)
@qwaiti27231 , Please find the attached file after the signature.
@qwaiti27231 , A new column like
Column = var _1 = if(isblank(countx(filter(('Table'), 'Table'[Date] <= eomonth(EARLIER('Table'[Date] ),-1) && 'Table'[Order Id]=EARLIER('Table'[Order Id])),'Table'[Order Id])),0,1)
return if( not(ISBLANK(COUNT('Table'[Order Id]))),_1,BLANK())
This is another option, but the answer does not match. Use the above one
Column 2 = var _1 = if(isblank(countx(filter(('Table'), 'Table'[Date] > eomonth(EARLIER([date]) ,-2) && 'Table'[Date] <= eomonth(EARLIER('Table'[Date] ),-1) && 'Table'[Order Id]=EARLIER('Table'[Order Id])),'Table'[Order Id])),0,1)
return if( not(ISBLANK(COUNT('Table'[Order Id]))),_1,BLANK())
@amitchandak ur logic is absolutely correct but its taking too long to query 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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |