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.
There are 'open order' transactions but the data sometimes not fully available daily.
So, to get a closure for a specific month, it has to be calculated from the last date (per each available group)
The group is also part of slicers. This should be easy but still I am only get it half way.
Here is an example data where country is a slicer. (let say another date slicer is in December so it should show November data)
date | product | open order | country |
10/25/2023 | apple | 3 | china |
10/25/2023 | apple | 32 | india |
11/13/2023 | apple | 1 | china |
11/13/2023 | apple | 4 | india |
11/15/2023 | apple | 7 | china |
11/15/2023 | apple | 11 | india |
11/22/2023 | apple | 6 | china |
11/22/2023 | apple | 12 | india |
11/23/2023 | apple | 5 | china |
11/24/2023 | apple | 9 | india |
12/11/2023 | apple | 2 | china |
12/18/2023 | apple | 12 | india |
Current DAXs I have:
Var __Result = CALCULATE( MAX(Table14[open order]) , 'Calendar'[Date] = __LastDate)
Try to change from Max(Table14[open order]) to Sum(Table14[open order]), does it works for your total to be 14?
Unfortuntely not.
@Maru
Suppose your have a calendar table connected, use this meaure:
Lastest Value =
Var __DatePre = EOMONTH( MAX('Calendar'[Date]), -1)
Var __LastDate =
CALCULATE(
MAX(Table14[date]),
'Calendar'[Date]< __DatePre
)
Var __Result = CALCULATE( MAX(Table14[open order]) , 'Calendar'[Date] = __LastDate)
RETURN
__Result
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Total has to be 14
@Maru
Try this measure:
Lastest Value =
Var __DatePre = EOMONTH( MAX('Calendar'[Date]), -1)
Var __Result =
SUMX(
VALUES( Table14[country]),
Var __LastDate = CALCULATE( MAX(Table14[date]),'Calendar'[Date]< __DatePre)
Var __OpenOder = CALCULATE( MAX(Table14[open order]) , 'Calendar'[Date] = __LastDate)
RETURN
__OpenOder
)
RETURN
__Result
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thank you for the update but I believe it requires either 'group by' or 'allexcept' to make it work with slicer. @Fowmy
@Maru
Sure, you may modify my solution to suit your needs. If what I shared was helpful, please go ahead and accept it as a solution.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
That is what I am after and yet be able to do it so post this thread ( in case it mat not possible )
Have to give credit to Fowmy. I adjusted the formula as blow. seems work. Hope it is helpful for you.
Would it work if you select 'Jan' ?