DAX formula to return data for last 6 months

[TannerBuck7]

I have a series of line and clustered column charts that I currently have showing only the last 6 months of data (column for each month). At this point I have to change the filters on these charts every month in order to update them to only show the last 6 months of data.

Is there any way to use a DAX formula to create a calculated column that I could use as a filter to only return last 6 months?

I used a DAX formula to create a column that I use as a filter for last 30 days (see below), so I was hoping to create something similar for returning the last 6 months. Using the last 168 days obviously doesn't work because it doesn't account for every day of the first month.

Last30days = IF(AND('Date'[Date]>=[Today]-30,'Date'[Date]<=[Today]),1,0)

[Anonymous]

I am using teh same method but i am gettign the error . please check here

i have created month idnex column and year month column i already have in my table

@NHar  @Anonymous @TannerBuck7 @konstantinos  please tell me the solution for this

[ced1971]

Here is a much simpler solution than the accepted one:

Last6Months = (YEAR(TODAY()) * 12 + MONTH(TODAY())) - (YEAR(Agenda[Date])*12+MONTH(Agenda[Date])) < 6

[NHar]

How would I go about not including the current month you're in?

Eg: It's June now, using this (WIth May selected) it still includes June's results. This is the Measure's DAX:

Last6Months = (YEAR(TODAY()) * 12 + MONTH(DATEVALUE("01 " & IssueData[Month Selected] & " 2022 " ))) - (YEAR(IssueData[Closed Date])*12+(MONTH(DATEVALUE("01 " & IssueData[Month Selected] & " 2022 " ))) < 6) 

 

The bit below is a measure that stores the month selected:

Month Selected = 

IF (

HASONEVALUE ( IssueData[Closed Date].[Month] ),

VALUES(IssueData[Closed Date].[Month])

)

[Anonymous]

I was able to solve this a slightly different way. In my own example I needed Last six complete months. To create the date ranges I was looking for, I created a seperate table and related that back to my calendar. This also gave me the ability to utilize both a drop down, and a Date Slicer that was only active when "Custom" was choosen in the drop down. 

Date Periods = 
Union(
    ADDCOLUMNS(
        DATESMTD('Calendar'[Dates]), "Type", "MTD", "Order", 1
        ),
    ADDCOLUMNS(
        DATESQTD('Calendar'[Dates]), "Type", "QTD", "Order", 2
        ),
    ADDCOLUMNS(
        DATESYTD('Calendar'[Dates]), "Type", "YTD", "Order", 3
        ),
    ADDCOLUMNS(
        PREVIOUSMONTH(DATESMTD('Calendar'[Dates])), "Type", "Last Month", "Order", 4
        ),
    ADDCOLUMNS(
        Previousquarter(DATESQTD('Calendar'[Dates])), "Type", "Last QTR", "Order", 5
        ),
    ADDCOLUMNS(
        PREVIOUSYEAR(DATESYTD('Calendar'[Dates])), "Type", "Last Year", "Order", 6
        ),
    ADDCOLUMNS(
        DATESINPERIOD('Calendar'[Dates], TODAY() - 30, 30, Day), "Type", "Last 30 Days", "Order", 7
        ),
    ADDCOLUMNS(
        DATESINPERIOD('Calendar'[Dates], TODAY() - 90, 90, Day), "Type", "Last 90 Days", "Order", 8
        ),
    ADDCOLUMNS(
        DATESINPERIOD('Calendar'[Dates], EOMONTH(TODAY(), -1), -6, MONTH), "Type", "Last 6 Months", "Order", 9
        ),
    ADDCOLUMNS(
        CALENDAR(MIN('Calendar'[Dates]), MAX('Calendar'[Dates])), "Type", "Custom", "Order", 10
    )
)

[Anonymous]

Hi, when i run this code, i get the following error

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

Can you please give an example - with the table. I am not able to understand how addcolumns is working here.

[Anonymous]

Elegant solution!  Exactly the sort of thing I was working on.  Thanks for sharing.

[worm00111]

This 1 line solution did the job for me:

Last 6 Months = IF(LedgerMovementFin[Posting Period] > EDATE(TODAY(), -6), LedgerMovementFin[Amount], 0)

[Anonymous]

This worked for me as well, but I would like to know why.  Could you explain Edate fuction? I couldn't actually find documentation on it. 

[konstantinos]

@TannerBuck7  You can try add a calculated column with something similar adjusted to your needs, also use it as slicer or filter in formulas

When refresh it auto adjust the periods based on TODAY()

Date Periods = 
VAR Datediff =
    1
        * ( 'Calendar'[Date] - TODAY () )
RETURN
    SWITCH (
        TRUE;
        AND ( Datediff <= 0; Datediff >= -180 ); "Last 6 Months";
        Datediff < 180; "Older than 6 Months"
    )

@ 

[TannerBuck7]

Thank you for your prompt reply.

If I am understanding this correctly the formula will just return data for the last 180 days right? The problem is that I need to be able to see data for every day of all 6 months (which will usually be more than 180 days) - with this formula it will not include some days of the first month depending on the current date.

Let me know If I misunderstood the formula or If I need to further clarify the issue at hand.

[TannerBuck7]

I want to create a calculated column that returns true or false depending on whether it is within the 6 month range or not.

I tried this: Last6Months = IF(MONTH('Date') >= (MONTH('Date') -6), "True", "False")

but it returned this error:  "The Error refers to multiple columns. Multiple columns cannot be converted to a scalar value."

[konstantinos]

@TannerBuck7 Not sure what you are trying. Can you define the period of 6 months?

In general when we say last six months we mean -180 days so we have standard period.

For example today is July 21st do you want to show February to today & when change to 1st of August show March to August?

Regarding your formula change it to ( which is 6 calendar months before & the future also since is bigger than 

Last6months =IF(MONTH('Calendar'[DateKey]) >=MONTH(TODAY()) -6;TRUE();FALSE())

If you try to describe with more details what are you trying to achive & what tables you have would help

[TannerBuck7]

Yes so since we are in July I would want to show data from February 1st to today, and as soon as we get to August 1st I would want the data to automatically change to show March 1st to the current date in August.

[konstantinos · Accepted solution]

@TannerBuck7 is a bit late so it must be a more efficient way but for now

Specially to avoid when year changes you will need a monthindex

Create a YearMonth Column ( if you don't have )

YearMonth = 
('Calendar'[YearKey] * 100 ) + MONTH('Calendar'[DateKey])

Then create the month index column

MonthIndex = 
VAR MonthRow = 'Calendar'[YearMonth]
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Calendar'[YearMonth] );
        FILTER ( 'Calendar'; 'Calendar'[YearMonth] <= MonthRow )
    )

and finally the Last6Months

Last6Months =
VAR TodayMonthIndex =
    CALCULATE (
        MAX ( 'Calendar'[MonthIndex] );
        FILTER ( 'Calendar'; TODAY () = 'Calendar'[DateKey] )
    )

VAR monthtocheck = Calendar[MonthIndex]

RETURN
    IF (
        AND ( monthtocheck >= TodayMonthIndex - 5; monthtocheck <= TodayMonthIndex );
        1;
        0
    )

More steps but when the year change you will still see six months before

Hope it helps

[Anonymous]

Hello,

How would I change this to show prior 6 months but starting from end of last month. For example 12/1/2019 - 05/31/2020.

[sangeethray]

You can change this

AND ( monthtocheck >= TodayMonthIndex - 5, monthtocheck <= TodayMonthIndex )

to

AND ( monthtocheck >= TodayMonthIndex - 6, monthtocheck < TodayMonthIndex )

[JohnD2]

Thanks for the comments, it is very valueable.

When i use this solution on my table, it sums all the values. 
In my situation i want to have the last value available for the 6 different months

I used in a calculated table the following calculated column:
MaxDate = MAXX(RELATEDTABLE(TestBalances);TestBalances[Date])

The only problem here is that it takes the very last value, of a range of dates.

I want to have the last value of a month

date table sample:
date                  yearmonthnumber
01-jan-2016     2016_01
02-jan-2016     2016_01
..
31-jan-2016     2016_01
01-feb-2016    2016_02
...

balance table sample:
date                 balance     type
01-jan-2016     2000         Green
15-jan-2016     2005         Green
26-jan-2016     2009         Green
01-feb-2016     2100         Green
15-feb-2016     2105         Green
24-feb-2016     2109         Green
01-mch-2016     2200         Green
15-mch-2016     2205         Green
17-mch-2016     2209         Green

The result that I want to have is:
26-jan-2016     2009         Green
24-feb-2016     2109         Green
17-mch-2016     2209         Green

Important note: when there is no value of the balance for a month, it should take the last available balance.
Any suggestions?

John

[TannerBuck7]

Thank you for your prompt response.

If I understand this correctly won't this mean that the formula will just return data for the last 180 days? The problem with this strategy is that I need month totals for every month in the last 6 month time range. If I just return the last 180 days it will only give me data for part of the first month in the time range, depending on what day of the month the 180 days start at.

Let me know If I misunderstood your solution or if I need to clarify what I am trying to do.