cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TannerBuck7 Frequent Visitor
Frequent Visitor

DAX formula to return data for last 6 months

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)

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
konstantinos Senior Member
Senior Member

Re: DAX formula to return data for last 6 months

@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

 

 

Konstantinos Ioannou
10 REPLIES 10
konstantinos Senior Member
Senior Member

Re: DAX formula to return data for last 6 months

@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"
    )

Konstantinos Ioannou
TannerBuck7 Frequent Visitor
Frequent Visitor

Re: DAX formula to return data for last 6 months

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.

 

 

TannerBuck7 Frequent Visitor
Frequent Visitor

Re: DAX formula to return data for last 6 months

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 Frequent Visitor
Frequent Visitor

Re: DAX formula to return data for last 6 months

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 Senior Member
Senior Member

Re: DAX formula to return data for last 6 months

@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

 

 

Konstantinos Ioannou
TannerBuck7 Frequent Visitor
Frequent Visitor

Re: DAX formula to return data for last 6 months

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.

 

 

Highlighted
konstantinos Senior Member
Senior Member

Re: DAX formula to return data for last 6 months

@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

 

 

Konstantinos Ioannou
JohnD2 Frequent Visitor
Frequent Visitor

Re: DAX formula to return data for last 6 months

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

worm00111 Occasional Visitor
Occasional Visitor

Re: DAX formula to return data for last 6 months

This 1 line solution did the job for me:

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