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.
Solved! Go to Solution.
Hi @Anonymous ,
YO= Year([DateOpened]) YC= Year([DateClosed])
2. Measures as listed :
Tickets opened by year = CALCULATE(SUMX(Table1,DISTINCTCOUNT(Table1[YO])),ALLEXCEPT(Table1,Table1[YO])) Tickets closed by year = IF(MAX([YC])=BLANK(),BLANK(),CALCULATE(SUMX(Table1,DISTINCTCOUNT(Table1[YC])),ALLEXCEPT(Table1,Table1[YC]))) Cumulative tickets open by year = CALCULATE(DISTINCTCOUNT(Table1[ID]),FILTER(ALL(Table1),Table1[DateOpened]<=MAX(Table1[DateOpened])),VALUES(Table1[YO])) Cumulative tickets closed by year = IF(MAX([DateClosed])=BLANK(),BLANK(),CALCULATE(DISTINCTCOUNT(Table1[ID]),FILTER(ALL(Table1),Table1[DateOpened]<=MAX(Table1[DateOpened])),VALUES(Table1[YC])))
Best regards,
Dina Ye
Hi @Anonymous ,
I’ve created a table as below, the status will show Off once ticket closed. Pbix attached here for your reference: https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/ET_34Gzt7mlPj0lSAgH_SMUBo6jOMXICBteSkCtetScB8A?e=kOibQc
Please refer to following formulas to generate the results:
MO = MONTH([DateOpened]) MONTH([DateClosed])
2. Measures as listed :
Tickets opened by month = CALCULATE(SUMX(Table1,DISTINCTCOUNT(Table1[MO])),ALLEXCEPT(Table1,Table1[MO])) Tickets closed by month = IF(MAX([MC])=BLANK(),BLANK(),CALCULATE(SUMX(Table1,DISTINCTCOUNT(Table1[MC])),ALLEXCEPT(Table1,Table1[MC]))) Cumulative tickets open by month = CALCULATE(DISTINCTCOUNT(Table1[ID]),FILTER(ALL(Table1),Table1[DateOpened]<=MAX(Table1[DateOpened])),VALUES(Table1[MO])) Cumulative tickets closed by month = IF(MAX([DateClosed])=BLANK(),BLANK(),CALCULATE(DISTINCTCOUNT(Table1[ID]),FILTER(ALL(Table1),Table1[DateOpened]<=MAX(Table1[DateOpened])),VALUES(Table1[MC])))
Best regards,
Dina Ye
Hi @v-diye-msft
I've given it a try but some of the measures don't seem to be working correctly:
Just to clarify what i'm tryig to end up with is something like this:
But the actual data contains tickets raised across multiple years.
I hope that makes is a bit clearer as to what I am trying to achieve.
Thanks for the support so far - Alex
Dina,
Thank you that's lots of help and has taught me lots, how would I expand this to cope with that data that spans more than one year?
Thanks
Hi @Anonymous ,
YO= Year([DateOpened]) YC= Year([DateClosed])
2. Measures as listed :
Tickets opened by year = CALCULATE(SUMX(Table1,DISTINCTCOUNT(Table1[YO])),ALLEXCEPT(Table1,Table1[YO])) Tickets closed by year = IF(MAX([YC])=BLANK(),BLANK(),CALCULATE(SUMX(Table1,DISTINCTCOUNT(Table1[YC])),ALLEXCEPT(Table1,Table1[YC]))) Cumulative tickets open by year = CALCULATE(DISTINCTCOUNT(Table1[ID]),FILTER(ALL(Table1),Table1[DateOpened]<=MAX(Table1[DateOpened])),VALUES(Table1[YO])) Cumulative tickets closed by year = IF(MAX([DateClosed])=BLANK(),BLANK(),CALCULATE(DISTINCTCOUNT(Table1[ID]),FILTER(ALL(Table1),Table1[DateOpened]<=MAX(Table1[DateOpened])),VALUES(Table1[YC])))
Best regards,
Dina Ye
Hi @Anonymous
If my above post helps, could you please consider Accept it as the solution to help the other members find it more quickly. thanks!
Best regards,
Dina Ye
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |