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

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

@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

View solution in original post

18 REPLIES 18
Anonymous
Not applicable

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

karlapudis_1-1655969260215.png

 

 

karlapudis_0-1655969091300.png

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

ced1971
New Member

Here is a much simpler solution than the accepted one:

 

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

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

)

 

 

 

Almercie
Frequent Visitor

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

 

 

 

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.

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

worm00111
New Member

This 1 line solution did the job for me:

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

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

@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

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.

 

 

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

 

@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

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.

 

 

@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
Anonymous
Not applicable

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.

You can change this

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

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

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.

 

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors