Report beginning of the month, but 6 months ago

[jorismo]

Hi all,

Currently I'm looking to make an report but I've got some issue's creating it.

The data is imported from an SQL server database, I've selected all the needed fields.

But now there it is 😉

the first part of the report must enter all the data between 2017-01-01 and first day of the month 6 months in the past. (so for today the open_date is between 2017-01-01 and 2020-06-01.

The second part of the report must have the open_date between 2020-06-01 (calculated above) and today

Is there a way to get the first day of the month 6 months in the past automatically?

Many many thanks!

[jorismo]

Thanks for your quick response but I don't get it done:

I've created the messure 'Datum -6' wich contains (Datum -6 = date(year(today()), month(today()) -6, 1) )

But how do I tell to my table that the Open_date is between 2017-01-01 and 'Datum-6'?

Thanks!

[v-jingzhang · Accepted solution]

Hi @jorismo 

You could create a measure like below to distinguish whether a date is in a date range. If in the range, the result is 1 otherwise 0. Then use the measure as a table visual filter with value is 1.

Flag = 
VAR firstDateSixMonthAgo = EDATE(DATE(YEAR(TODAY()),MONTH(TODAY()),1),-6)
RETURN
IF(MAX('Table'[Date]) < firstDateSixMonthAgo && MAX('Table'[Date]) >= DATE(2017,1,1),1,0)

 

Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[amitchandak]

@jorismo , begining date 6 month before

measure  = date(year(today()), month(today()) -6, 1)

rolling 6 month data example

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-6,MONTH))
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(today(),-1)+1,-6,MONTH))