Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need calculate a function to print the variation (business plans) to 2021 and 2022 but i need compare so sar this month
I use this function but not work
If ([Calendario.Mes]<=Month(today(0)),sum({<[Calendario.Año]={$(=Max([Calendario.Año]))}>}[Planescomerciales])
/
If ([Calendario.Mes]<=Month(today(0)),sum({<[Calendario.Año]={$(=Max([Calendario.Año])-1)}>}[Planescomerciales])
Hi @Sergio0820 I would suggest you to simply write YTD set expression instead of IF
This will give you proper YTD vs Previous YTD comparison
Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)
/
Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(AddYears(Max(DateNum),-1))))<=$(=AddYears(Max(DateNum),-1))"}>} Sales)
For more details you may follow this link
Hi @Sergio0820 , please check if this works for you, it just uses a common set analysis expression,
Script:
Data:
Load * INLINE [
Fecha, Valor
01-01-2021, 453
01-02-2021, 963
01-03-2021, 240
01-04-2021, 548
01-05-2021, 341
01-06-2021, 457
01-07-2021, 181
01-08-2021, 306
01-09-2021, 900
01-10-2021, 273
01-11-2021, 557
01-12-2021, 743
01-01-2022, 571
01-02-2022, 496
01-03-2022, 674
01-04-2022, 681
01-05-2022, 389
01-06-2022, 831
01-07-2022, 354
01-08-2022, 961
01-09-2022, 115
01-10-2022, 796
01-11-2022, 530
];
Data2:
Load
Fecha,
year(Fecha) as Year,
Month(Fecha) as Month,
Valor
Resident Data;
drop table Data;
Expression:
sum(Valor)
/
sum( {$<Year = {$(#=Only(Year)-1)}>} Valor )
As you can see in the picture , you just have to select one Year and one Month
Check about set analysis here:
You can add max function to not to have to select a month and a year