Forum Discussion

AsherB's avatar
AsherB
New Member
8 years ago

Ticket Backlog

Hi,   I'm pretty new to Power Bi and learning a lot from answers given here. I'm trying to create a ticket backlog.   Following are the relevant columns: Table: 'case-export' Case # Created(c...
v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
8 years ago

Hi AsherB,

 

Please Create a calculated column Week = WEEKNUM('Calendar'[Date],1) in Calendar table.

 

Then you can create a measure like below:

 

Measure = CALCULATE(COUNT('case-export'[Case#]),FILTER('case-export',MAX('Calendar'[Week])>=WEEKNUM('case-export'[Created]) && MAX('Calendar'[Week])<WEEKNUM('case-export'[Resolved])))

 

You an download the attached pbix file to have a look.

 

Best Regards,
QiuyunYu

 

 

AsherB's avatar
AsherB
New Member
8 years ago

Hi Anonymous

 

Thank you for the detailed solution but it doesn't return the correct number I'm afraid.

I've looked over the Power bi file you've attached and it seems to be working correctly.

 

On my file, it returns a much lower number than expected.

 

We have cases that remain open for few weeks, maybe that's the issue?

 

Can you explain to me the parts of the measure you've written?

 

Kind regards,

Asher

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    8 years ago

    Hi AsherB,

     

    As you said it returns incorrect values in your pbix file? Would you please share your pbix file with us if possible? Or you can use some dummy data to clarify the issue. 

     

    The DAX I written is to compare each calendar week for each record's start week and end week to decide whether it's open in current calendar week. 

     

    Best Regards,
    Qiuyun Yu