Addcolumns YTD, Previous Month, Year to Previous Month End

[Giorgi1989]

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.

[Anonymous]

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.

[AlexisOlson]

Another method is to use calculation groups.

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

[Whitewater100 · Accepted solution]

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

1,

0

)

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

[Giorgi1989]

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

[Whitewater100]

Hi:

Are you able to mark as solution? Thanks..

[Giorgi1989]

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