Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello friends,
I have problem, I am constructing a measure that cumulate the sum of something, with this formula, BUT it doesent restart with every year:
ACUM META =
CALCULATE(
[MC_CANT VENTAS PRESUPUESTADAS POR CATEGORIA];
FILTER(
ALL(DIM_PERIODO_COMERCIAL);
DIM_PERIODO_COMERCIAL[ANNO_COMERCIAL]<=MAX(DIM_PERIODO_COMERCIAL[ANNO_COMERCIAL])))
I am working with Direct Query. In my "time" dimension I dont have a date field.
An example of my "time" dimension:
Thanks a lot if you can help me
Cheers
Fred
Solved! Go to Solution.
Hello
Enter a calculated column formula to create a Date field. This can also be achieved in Query Editor, but I'm not sure if it's allowed in direct query mode
Closes 1*("1/"&Datos[Mes_Comercial]&"/"&[Anno Comercial])
Create a relationship from this Date column to the Date column of the calendar table.
Write this measure
ACUM META - CALCULATE([MC_CANT SALES BUDGETED BY CATEGORY];DATESYTD(Calendar[Date],"31/12"))
I hope this helps.
Hi @frenst ,
We can use following measure to meet your requirement:
ACUM META =
CALCULATE (
[MC_CANT VENTAS PRESUPUESTADAS POR CATEGORIA];
FILTER (
ALL ( DIM_PERIODO_COMERCIAL );
DIM_PERIODO_COMERCIAL[ANNO_COMERCIAL]
<= MAX ( DIM_PERIODO_COMERCIAL[ANNO_COMERCIAL] )
&& DIM_PERIODO_COMERCIAL[ANNO_COMERCIAL]
>= ROUNDDOWN ( MAX ( DIM_PERIODO_COMERCIAL[ANNO_COMERCIAL] ) / 100; 0 ) * 100 + 1
)
)
Best regards,
Hello
Enter a calculated column formula to create a Date field. This can also be achieved in Query Editor, but I'm not sure if it's allowed in direct query mode
Closes 1*("1/"&Datos[Mes_Comercial]&"/"&[Anno Comercial])
Create a relationship from this Date column to the Date column of the calendar table.
Write this measure
ACUM META - CALCULATE([MC_CANT SALES BUDGETED BY CATEGORY];DATESYTD(Calendar[Date],"31/12"))
I hope this helps.
Thanks, the logic you propose worked fine. I construct another time dimension with the date field, then create a relationship with my table, and create a new measure that cummulate fine.
Thanks
You are welcome.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |