Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi All
how to rewrite in dax format.
if (A.Orderdate < @CalendarDate AND (A.purchasedate > @CalendarDate OR A.purchaseDate IS NULL))
OR (A.Orderdate > @CalendarDate) then count(id) else 0
vs
Solved! Go to Solution.
how can add i more filter condition calendardate = startofmonth(_OrderDate)
Calc Column =
VAR _OrderDate = YourTableName[Orderdate]
VAR _PurchaseDate = YourTableName[PurchaseDate]
VAR _CalendarDate = YourTableName[CalendarDate]
VAR _1 = _OrderDate < _CalendarDate
VAR _2 = _PurchaseDate > _CalendarDate || _PurchaseDate = 0
VAR _3 = _OrderDate > _CalendarDate
RETURN
IF ( _1 && _2 && _3 = TRUE , COUNT ( YourTableName[ID] ) , 0 )
Hi @refint_650
Can you try something like the following calculated column:
Calc Column =
VAR _OrderDate = YourTableName[Orderdate]
VAR _PurchaseDate = YourTableName[PurchaseDate]
VAR _CalendarDate = YourTableName[CalendarDate]
VAR _1 = _OrderDate < _CalendarDate
VAR _2 = _PurchaseDate > _CalendarDate || _PurchaseDate = 0
VAR _3 = _OrderDate > _CalendarDate
RETURN
IF ( _1 && _2 && _3 = TRUE , COUNT ( YourTableName[ID] ) , 0 )
You may need to make slight adjustments to get the syntax right but hopefully it works out.
Thanks heaps,
Theo
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
how can add i more filter condition calendardate = startofmonth(_OrderDate)
Calc Column =
VAR _OrderDate = YourTableName[Orderdate]
VAR _PurchaseDate = YourTableName[PurchaseDate]
VAR _CalendarDate = YourTableName[CalendarDate]
VAR _1 = _OrderDate < _CalendarDate
VAR _2 = _PurchaseDate > _CalendarDate || _PurchaseDate = 0
VAR _3 = _OrderDate > _CalendarDate
RETURN
IF ( _1 && _2 && _3 = TRUE , COUNT ( YourTableName[ID] ) , 0 )
@refint_650 will send it to you when I get in front of computer. on phone atm sorry.
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
@refint_650 did the formula work for you? If so, please mark as solution.
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
It worked greate. but i forgot add one more condition to same logic
only show data where calendardate = startofmonth(_OrderDate)
@refint_650 still on phone so apologies for any syntax errors! Hope this helps!
Calc Column =
VAR _OrderDate = YourTableName[Orderdate]
VAR _PurchaseDate = YourTableName[PurchaseDate]
VAR _CalendarDate = YourTableName[CalendarDate]
VAR _StartOfMonth = STARTOFMONTH ( YourTableName[CalendarDate] )
VAR _1 = _OrderDate < _CalendarDate
VAR _2 = _PurchaseDate > _CalendarDate || _PurchaseDate = 0
VAR _3 = _OrderDate > _CalendarDate
VAR _4 = _StartOfMonth = _CalendarDate
RETURN
IF ( _1 && _2 && _3 && _4 = TRUE , COUNT ( YourTableName[ID] ) , 0 )
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Much Appreciated. I will slightly tweak expression as year as primary & Month(optional) as secondary slicers .
ex: I'm doing VAR _1 = _OrderDate < selectedvalue( _CalendarDate)
All good! Look forward to hearing from you.
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
User | Count |
---|---|
84 | |
69 | |
69 | |
65 | |
54 |
User | Count |
---|---|
93 | |
93 | |
91 | |
76 | |
69 |