topic Re: Addcolumns YTD, Previous Month, Year to Previous Month End in DAX Commands and Tips

Addcolumns YTD, Previous Month, Year to Previous Month End

Giorgi1989 — Fri, 22 Apr 2022 12:16:02 GMT

Dear All,

I am trying to re-create a time selection slicer (see the DAX formula below):

There are 24 months in 'Calendar Year/Month', from Jan 2021 to Dec 2022. (Those future months include only Budget figures).

What I want to achieve:

I wish that DATESMTD, DATEYTD etc. to display figures in relation to current month, not the MAX in the date table (i.e. Dec 2022).

Time Selection =

Union(

ADDCOLUMNS(

DATESMTD('Date'[Calendar Year/Month]),

"Selection", "MTD"

ADDCOLUMNS(

DATESYTD('Date'[Calendar Year/Month]),

"Selection", "YTD"

ADDCOLUMNS(

PREVIOUSMONTH(DATESMTD('Date'[Calendar Year/Month])),

"Selection", "Previous Month"

ADDCOLUMNS(

DATESINPERIOD('Date'[Calendar Year/Month],MAX('Date'[Calendar Year/Month]),-12,Month),

"Selection", "Rolling 12 months"

))

With this formula above I get a view as follow:

I have tried DATESINPERIOD, DATESBETWEEN etc. functions to no avail.

Any tip/suggestion would be highly appreciated.

Thank you, and wish you all a fabulous weekend.

Re: Addcolumns YTD, Previous Month, Year to Previous Month End

Giorgi1989 — Fri, 22 Apr 2022 13:08:19 GMT

Update: I actually figured it out. Everything works perfectly now!!!

Re: Addcolumns YTD, Previous Month, Year to Previous Month End

Whitewater100 — Fri, 22 Apr 2022 13:31:54 GMT

Hi:

You can try this pattern. I'm using my columns as I can't see yours, but this should help you on this one.This table is in a separate bi-directional relationship with a second date table (DATESII.) DATESII Table is like this:

DatesII = CALENDAR(MIN('Sales Orders'[SHIPPED_DATE]), MAX('Sales Orders'[SHIPPED_DATE])

DatesII is an additional Date Table.

)

Add Calc Col to DATESII

Date Slicer Filter =

IF(SELECTEDVALUE('Date Periods'[Type]) = "Custom",

)

I'll paste a couple images below the DAX. The slicer formula is a separate table (MTD,QTD,YTD,etc) and I will paste that in last. I'm providing more than asked for as I'm anticipating you will want to use these other pieces..

YOUR QUESTION: -note use of second Date Table.

Date Periods =

UNION(ADDCOLUMNS(

DATESMTD(DatesII[Date]),

"Type", "MTD",

"Order", 1

ADDCOLUMNS(

DATESQTD(DatesII[Date]),

"Type", "QTD",

"Order", 2),

ADDCOLUMNS(

DATESYTD(DatesII[Date]),

"Type", "YTD",

"Order", 3),

ADDCOLUMNS(

PREVIOUSYEAR(DATESYTD(DatesII[Date])),

"Type", "Last Year",

"Order", 6),

ADDCOLUMNS(

PREVIOUSMONTH(DATESMTD(DatesII[Date])),

"Type", "Last Month",

"Order", 4),

ADDCOLUMNS(

PREVIOUSQUARTER(DATESQTD(DatesII[Date])),

"Type", "Last Quarter",

"Order", 5),

ADDCOLUMNS(

CALENDAR(MIN(Dates[Date]), MAX(DatesII[Date])),

"Type", "All Data",

"Order", 7)

)

From Period Table. * I am using an older file for demo purposes that's why dates look odd or you see warning triangles.

MODEL

Slicer Table -

MTD/QTD/YTD Selection =

VAR TodayDate = TODAY()

VAR YearStart = CALCULATE(STARTOFYEAR('Sales Orders'[Month & Year]), YEAR('Sales Orders'[Month & Year]) = YEAR(TodayDate))

VAR QtrStart = CALCULATE(STARTOFQUARTER('Sales Orders'[Month & Year]), YEAR('Sales Orders'[Month & Year]) = YEAR(TodayDate), QUARTER('Sales Orders'[Month & Year]) = QUARTER(TodayDate))

VAR MnthStart = CALCULATE(STARTOFMONTH('Sales Orders'[Month & Year]), YEAR('Sales Orders'[Month & Year]) = YEAR(TodayDate), MONTH('Sales Orders'[Month & Year]) = MONTH(

TodayDate))

VAR Result =

UNION(ADDCOLUMNS(

CALENDAR(YearStart, TodayDate),

"Selection", "YTD"),

ADDCOLUMNS(

CALENDAR(QtrStart, TodayDate),

"Selection", "QTD"

ADDCOLUMNS(

CALENDAR(MnthStart, TodayDate),

"Selection", "MTD"

))

RETURN

Result

Re: Addcolumns YTD, Previous Month, Year to Previous Month End

AlexisOlson — Sat, 23 Apr 2022 22:12:59 GMT

Another method is to use calculation groups.

https://www.sqlbi.com/articles/introducing-calculation-groups/

Re: Addcolumns YTD, Previous Month, Year to Previous Month End

Giorgi1989 — Mon, 25 Apr 2022 07:39:17 GMT

Hi, thank you. This is pretty close to what I did! 🙂

Re: Addcolumns YTD, Previous Month, Year to Previous Month End

Anonymous — Wed, 27 Apr 2022 06:27:34 GMT

Hi Giorgi1989

I am so glad to hear that your problem has been solved , then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Addcolumns YTD, Previous Month, Year to Previous Month End

Whitewater100 — Wed, 27 Apr 2022 12:51:35 GMT

Hi:

Are you able to mark as solution? Thanks..