Forum Discussion
DAX IDEA
Hi community,
Im trying to find a solution for a measure I created that allows me to have the SUM of the last 5 months from the chosen month in the slicer even if I have more months selected in the Date slicer. For example:
On the slicer I have marked from "january 2018" to "december 2018" so I need to have the SUM from the last date chosen (December 2018) to (August 2018)... (In tha case I have the 5 months I need only)
and if I choose to "january 2018" to "november 2018" get the sum from november to July... and so on...
Any help or advice would be so much appreciated.
5 LAST MONTHS ALTO BRUT RE TEST 2 =
IF (
SELECTEDVALUE ( 'LLAVE TIPO'[Tipo]; "Negocio" ) = "Negocio";
CALCULATE (
SUM ( 'Base Ventas Netas'[Cantidad] );
'Base Ventas Netas'[Cantidad] = 1;
'Base Ventas Netas'[Tabla Pyme] = ">600";FIRSTDATE(DATESINPERIOD('Base Ventas Netas'[Mes];LASTDATE('Base Ventas Netas'[Mes]);5;MONTH)
));
CALCULATE (
SUM ( 'Base Ventas Netas'[Cantidad] );
'Base Ventas Netas'[Cantidad] = 1;
'Base Ventas Netas'[RE/RP] = "RESTD";
'Base Ventas Netas'[ISEE] IN { "ABC1"; "C2" };
'Base Ventas Netas'[Tabla Persona]
IN { "<400"; "<600"; "<700"; "<800"; "<900"; ">900" };FIRSTDATE(DATESINPERIOD('Base Ventas Netas'[Mes];LASTDATE('Base Ventas Netas'[Mes]);5;MONTH
)
))
+ CALCULATE (
SUM ( 'Base Ventas Netas'[Cantidad] );
'Base Ventas Netas'[ISEE] = "NO DATA";
'Base Ventas Netas'[Tabla Persona] = ">900";
'Base Ventas Netas'[Cantidad] = 1;
'Base Ventas Netas'[RE/RP] = "RESTD";FIRSTDATE(DATESINPERIOD('Base Ventas Netas'[Mes];LASTDATE('Base Ventas Netas'[Mes]);5;MONTH
))))
You need this Anonymous . ENJOY!!!
XXXX XXXX XXXX =SELECTEDVALUE ( 'LLAVE TIPO'[Tipo]; "Negocio" ) = "Negocio";CALCULATE (SUM('Base Ventas Netas'[Cantidad]);'Base Ventas Netas'[Cantidad] <>-1;'Base Ventas Netas'[RE/RP]="RPSTD";'Base Ventas Netas'[Tabla Pyme] = ">600";ALL('Master Calendar);DATESINPERIOD('Base Ventas Netas'[Mes];MAX('Base Ventas Netas'[Mes]);-6;MONTH));CALCULATE (SUM( 'Base Ventas Netas'[Cantidad] );'Base Ventas Netas'[Cantidad] <>-1;'Base Ventas Netas'[RE/RP] = "RPSTD";'Base Ventas Netas'[ISEE] IN { "ABC1"; "C2" };'Base Ventas Netas'[Tabla Persona]IN { "<400"; "<600"; "<700"; "<800"; "<900"; ">900" };ALL('Master Calendar;DATESINPERIOD('Base Ventas Netas'[Mes];MAX('Base Ventas Netas'[Mes]);-6;MONTH))+ CALCULATE (SUM( 'Base Ventas Netas'[Cantidad] );'Base Ventas Netas'[ISEE] = "NO DATA";'Base Ventas Netas'[Tabla Persona] = ">900";'Base Ventas Netas'[Cantidad] <>-1;'Base Ventas Netas'[RE/RP] = "RPSTD";ALL('Master Calendar);DATESINPERIOD('Base Ventas Netas'[Mes];MAX('Base Ventas Netas'[Mes]);-6;MONTH)))
2 Replies
HI Anonymous ,
Can you please share some sample data for test? it is hard to test with dax formula without sample data.
How to Get Your Question Answered Quickly
In addition, you can also take a look at the following link to use date function to define filter range and calculate:
Time Intelligence "The Hard Way" (TITHW)
Regards,Xiaoxin Sheng
You need this Anonymous . ENJOY!!!
XXXX XXXX XXXX =SELECTEDVALUE ( 'LLAVE TIPO'[Tipo]; "Negocio" ) = "Negocio";CALCULATE (SUM('Base Ventas Netas'[Cantidad]);'Base Ventas Netas'[Cantidad] <>-1;'Base Ventas Netas'[RE/RP]="RPSTD";'Base Ventas Netas'[Tabla Pyme] = ">600";ALL('Master Calendar);DATESINPERIOD('Base Ventas Netas'[Mes];MAX('Base Ventas Netas'[Mes]);-6;MONTH));CALCULATE (SUM( 'Base Ventas Netas'[Cantidad] );'Base Ventas Netas'[Cantidad] <>-1;'Base Ventas Netas'[RE/RP] = "RPSTD";'Base Ventas Netas'[ISEE] IN { "ABC1"; "C2" };'Base Ventas Netas'[Tabla Persona]IN { "<400"; "<600"; "<700"; "<800"; "<900"; ">900" };ALL('Master Calendar;DATESINPERIOD('Base Ventas Netas'[Mes];MAX('Base Ventas Netas'[Mes]);-6;MONTH))+ CALCULATE (SUM( 'Base Ventas Netas'[Cantidad] );'Base Ventas Netas'[ISEE] = "NO DATA";'Base Ventas Netas'[Tabla Persona] = ">900";'Base Ventas Netas'[Cantidad] <>-1;'Base Ventas Netas'[RE/RP] = "RPSTD";ALL('Master Calendar);DATESINPERIOD('Base Ventas Netas'[Mes];MAX('Base Ventas Netas'[Mes]);-6;MONTH)))