CALCULATE items from today up to 30 days before

[BaronSdG]

Hello,

I need to count all the rows which are both Closed (this is working) and have a Closed date old at maximum of 30 days from today. This is the formula.

CALCULATE([Count_Rows_J],'Jira Datalake'[Status Open vs Closed items (groups)] = "Closed")

Can you help me? Thanks

[amitchandak]

@BaronSdG , Try like this with date table

Rolling 30 = CALCULATE([Count_Rows_J],DATESINPERIOD('Date'[Date],today(),-30,Dau)'Jira Datalake'[Status Open vs Closed items (groups)] = "Closed")

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[BaronSdG]

Wow thanks both @parry2k and  @Greg_Deckler for the fast response! Unfortunately, both solutions are not working. I am posting here the full code I have tried after your response

_Count_Closed_30days_dynStr =

VAR _30date = TODAY() - 30

VAR created = CALCULATE([Count_Rows_J],'Jira Datalake'[Status Open vs Closed items (groups)] = "Closed", 'Jira Datalake'[Closed] >= _30date)

RETURN "-" & COALESCE(created,0) // shows '0' instead of "(empty)"

Edit

Also, I think you use different simbles of < and > in your formulas

[Greg_Deckler]

@BaronSdG - What is [Count_Rows_J] ? is that a measure? If so can you post the code? Also, posting sample data and expected output would assist greatly. 

First check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[BaronSdG · Accepted solution]

@Greg_Deckler I have used both formulas and I was able to fix yours, which was just missing the table name after FILTER. This formula is now working.

_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))

RETURN "-" & COALESCE(created,0) // shows '0' instead of "(empty)"

Regarding your question, yes [Count_Rows_J] is a measure which count the numer of rows of the table

[Greg_Deckler]

My bad, that's what happens when I wing it.

[parry2k]

@BaronSdG when you said not working, are you getting the wrong result and there is an error in the DAX measure? It is not clear what is not working.

[parry2k]

@BaronSdG create a variable then use that in your formula

Measure = 
VAR __date = TODAY() - 30
RETURN
CALCULATE([Count_Rows_J],'Jira Datalake'[Status Open vs Closed items (groups)] = "Closed",
'Jira Datalake'[Closed Date] <= __date)

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.⚡

[Greg_Deckler]

@BaronSdG - Perhaps:

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