Show open items from the last 30 days

[BaronSdG]

Hello,

On another thread I was helped with a formula to compute my closed items like the one below. I have tried it again for my open items, but is not working and the difference is that when selecting the column with the date ('Jira Datalake'[Created]), Power BI also asks me to add an extra field (such as .[Date]), but it's not working. I have tried leaving that field empty, using .date, .day but still is not working. 

_Count_Open_30days_dynStr =

VAR created = CALCULATE([Count_Rows_J],FILTER('Jira Datalake','Jira Datalake'[Status Open vs Closed items (groups)] = "Closed" && 'Jira Datalake'[Created].[Date] >= TODAY()-30))

Can you help me?

[v-jayw-msft]

Hi @BaronSdG ,

Based on my test, no matter if you used .[date] or not, the formula should give you right result. It's better to share some sample data to us.

Best Regards,

Jay

[BaronSdG]

Hi @v-jayw-msft ,

Let me share with you the formula which is working for the closed items:

Measure 1:

Count_Rows_J = COUNTROWS('Jira Datalake')

Measure 2:

_Count_Closed_30days_dynStr =

VAR created = CALCULATE([Count_Rows_J],FILTER('Jira Datalake','Jira Datalake'[Status Open vs Closed items (groups)] = "Closed" && 'Jira Datalake'[Closed] >= TODAY()-30))

Measure 3:

_Count_Open_30days_dynStr =

VAR created = CALCULATE([Count_Rows_J],FILTER('Jira Datalake','Jira Datalake'[Status Open vs Closed items (groups)] = "Closed" && 'Jira Datalake'[Created] >= TODAY()-30))

[v-jayw-msft]

Hi @BaronSdG ,

The syntax of your formula is incorrect. You are using VAR but not using Return after it. In addition, i can't found any difference between measure2 and measure3. It's better to show some sample data and expected result to us so that we can wirte DAX for you.

Best Regards,

Jay

[parry2k]

@BaronSdG you should remove .[Date] part, it is a hidden table, as best practice, you should turn it off and use date dimension for you calculations. As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-ca...

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[BaronSdG]

So do I have to remove only .[Date] (as below) or do I also need to do the steps you have mentioned later on?

VAR created = CALCULATE([Count_Rows_J],FILTER('Jira Datalake','Jira Datalake'[Status Open vs Closed items (groups)] = "Closed" && 'Jira Datalake'[Created] >= TODAY()-30))