Total sales - MTD current year vs MTD last year with time filter

[omarevp]

gHello guys. There are a couple of things I'm trying to do here:

First, in my Calendar table, I need a calculated column which shows "Current month", "previous month" "2 months ago", etc.. Ex:

Date	Year	Month	Day	Relative month
12/31/2018	2018	December	31	2 months ago
1/1/2019	2019	January	1	previous month
1/2/2019	2019	January	2	previous month
2/15/2019	2019	February	15	Current month
2/16/2019	2019	February	16	Current month
2/17/2019	2019	February	17	Current month
3/11/2019	2019	March	11	Next month
3/12/2019	2019	March	12	Next month
3/13/2019	2019	March	13	Next month

The last column is what I need, a calculated column that will change every new month.

Second, I will use that calculated column to do a filter selector, in which my user will be able to select "Current month", "Previous month", etc. in order to check total sales from a Card visual to current year, and another Card visual which shows last year total sales.

Right now, this is my measure for Total Sales Last year:

First measure:

c_Ventas MTD =

IF (MIN(CALENDARIO[Fecha_Master])<=CALCULATE(MAX(VENTAS_DIARIAS[Fecha comercial]),ALL(VENTAS_DIARIAS)),

 CALCULATE(SUM(VENTAS_DIARIAS[Total]),DATESMTD(CALENDARIO[Fecha_Master]))

)

Second measure:

c_Total Venta mes A.Ant =

VAR DataMaxDate =

 CALCULATE ( MAX ( VENTAS_DIARIAS[Fecha comercial] ), ALL ( VENTAS_DIARIAS ) )

RETURN

 CALCULATE (

 [c_Ventas MTD],

 SAMEPERIODLASTYEAR (

 INTERSECT (

 VALUES ( CALENDARIO[Fecha_Master] ),

 DATESBETWEEN ( CALENDARIO[Fecha_Master], BLANK (), DataMaxDate )

 )

 )

 )

The thing with this is, when I try to apply a Time filter like "month", because it shows an error with

the SAMEPERIODLASTYEAR function. And as I say, im trying to do a filter selector which allows me to see Total sales

from current year MTD (if I select "current month" that shows only this month; if I select "previous month" it shows previous month), but also Total sales from LAST YEAR MTD (if I select "current month" that shows only this month LAST YEAR; if I select "previous month" it shows previous month LAST YEAR

Is there a way to do that?

Thanks in advance, I know you can help me, this should be an easy one.

Omar.

[v-frfei-msft · Accepted solution]

Hi @omarevp,

For the Relative month column, we can create a caluclated column as below to get that.

Relative month2 = 
VAR _diff =
    DATEDIFF ( TODAY (), 'Table1'[Date], MONTH )
RETURN
    SWITCH (
        _diff,
        -2, "2 months ago",
        -1, "Previous month",
        0, "Current month",
        1, "Next Month"
    )

For your second question, kindly share your pbix to me.

Regards,

Frank

[CheenuSing]

Hi @omarevp

Can you plaease post data and expected results on Google Drive or OneDrive and share the link here to formulate a solution.

Cheers

CheenuSing