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.
Hello,
I need help with DAX for the following problem:
In sum I want to know how many active contracts I have up to a certain date (state = active ou cutted). The data is in an archive table, so everytime there's a change in the contract, that table is updated (PK is Contract No and Version No).
I've tried a lot of ways, but most of them don't fully work in one way or another and I'm already lost in everything I've tried.
Here is an example of the data and the logic I was following to write the formulas
Thanks!
Solved! Go to Solution.
Hi @marianammartins ,
Please try:
Active||Cutted' =
var _a = MAX('DimDate'[Date])
var _b = FILTER(ALL('New Table'),[Archive Date]<=_a)
var _c = COUNTROWS(FILTER(_b,[Archive Date]=MAXX(FILTER(_b,[Contrato No]=EARLIER('New Table'[Contrato No])),[Archive Date])&&OR([State]="Active",[State]="Cutted")))
return _c
Finished' =
var _a = MAX('DimDate'[Date])
var _b = FILTER(ALL('New Table'),[Archive Date]<=_a)
var _c = COUNTROWS(FILTER(_b,[Archive Date]=MAXX(FILTER(_b,[Contrato No]=EARLIER('New Table'[Contrato No])),[Archive Date])&&[State]="Finished"))
return _c
Pre' =
var _a = MAX('DimDate'[Date])
var _b = FILTER(ALL('New Table'),[Archive Date]<=_a)
var _c = COUNTROWS(FILTER(_b,[Archive Date]=MAXX(FILTER(_b,[Contrato No]=EARLIER('New Table'[Contrato No])),[Archive Date])&&[State]="Pre"))
return _c
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @marianammartins ,
Please try:
Active||Cutted' =
var _a = MAX('DimDate'[Date])
var _b = FILTER(ALL('New Table'),[Archive Date]<=_a)
var _c = COUNTROWS(FILTER(_b,[Archive Date]=MAXX(FILTER(_b,[Contrato No]=EARLIER('New Table'[Contrato No])),[Archive Date])&&OR([State]="Active",[State]="Cutted")))
return _c
Finished' =
var _a = MAX('DimDate'[Date])
var _b = FILTER(ALL('New Table'),[Archive Date]<=_a)
var _c = COUNTROWS(FILTER(_b,[Archive Date]=MAXX(FILTER(_b,[Contrato No]=EARLIER('New Table'[Contrato No])),[Archive Date])&&[State]="Finished"))
return _c
Pre' =
var _a = MAX('DimDate'[Date])
var _b = FILTER(ALL('New Table'),[Archive Date]<=_a)
var _c = COUNTROWS(FILTER(_b,[Archive Date]=MAXX(FILTER(_b,[Contrato No]=EARLIER('New Table'[Contrato No])),[Archive Date])&&[State]="Pre"))
return _c
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @marianammartins ,
Please try:
First, create a new table:
Up to Date = ADDCOLUMNS(FILTER(CALENDAR(EOMONTH(MIN('New Table'[Archive Date]),0),EOMONTH(MAX('New Table'[Archive Date]),0)),[Date]=EOMONTH([Date],0)),"Up To Date","up to "&FORMAT([Date],"DD MMM YY"))
Output:
Then create a table visual:
Pre' =
var _a = MAX('Up to Date'[Date])
var _b = FILTER('New Table',[Archive Date]<=_a)
var _c = COUNTROWS(FILTER(_b,[Archive Date]=MAXX(FILTER(_b,[Contrato No]=EARLIER('New Table'[Contrato No])),[Archive Date])&&[State]="Pre"))+0
return _c
Finished' =
var _a = MAX('Up to Date'[Date])
var _b = FILTER('New Table',[Archive Date]<=_a)
var _c = COUNTROWS(FILTER(_b,[Archive Date]=MAXX(FILTER(_b,[Contrato No]=EARLIER('New Table'[Contrato No])),[Archive Date])&&[State]="Finished"))+0
return _c
Active||Cutted' = var _a = MAX('Up to Date'[Date])
var _b = FILTER('New Table',[Archive Date]<=_a)
var _c = COUNTROWS(FILTER(_b,[Archive Date]=MAXX(FILTER(_b,[Contrato No]=EARLIER('New Table'[Contrato No])),[Archive Date])&&OR([State]="Active",[State]="Cutted")))+0
return _c
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
That works a bit better thanks, but if I put the measure in the y-axis of a bar chart and my calendar table in the x-axis, it doesn't return the desired results
Hi @marianammartins ,
You need to put the 'Up to Date'[Date] in the X-axis:
Output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I'm using the Calendar (DimDate) as the "Up to Date" because the goal is to know the number of active contracts at any given date
Hi @marianammartins ,
Please try:
Unpivot the columns:
Output:
Then apply the measure:
Finished = COUNTROWS(FILTER('Table',[State]="Finished"&&[Value]<>BLANK()))+0
Pre = COUNTROWS(FILTER('Table',[State]="Pre"&&[Value]<>BLANK()))+0
Active||Cutted = COUNTROWS(FILTER('Table',OR([State]="Active",[State]="Cutted")&&[Value]<>BLANK()))+0
Output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello
Thank you so much for trying to help and I'm sorry I didn't explain myself correctly. The only columns that are part of the table are the ContractNo, State and Archive Date... the other ones are only examples of the logic I was following, I did that to try and breakdown the steps I would need to take into to account to achieve the correct results
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 |
---|---|
97 | |
95 | |
76 | |
72 | |
65 |
User | Count |
---|---|
136 | |
109 | |
104 | |
82 | |
73 |