Forum Discussion
Get right measure
Hi, I'm sorry finally I managed to do ir but usingSQL instead, with DAX I found it impossible,
I show you my function.
Maybe there is a possibility on DAX but I couldn't:
SELECT *, CASE WHEN t3.MES = 4 THEN IMPORTEPRIMA ELSE sum(T3.importeprima) OVER(PARTITION by t3.aniofiscal ORDER BY Convert(int, SUBSTRING(t3.fechaMas500, 0, 5)) asc, convert(int, t3.mes) ROWS BETWEEN unbounded preceding and current row) END as TotalAcumulado from (
SELECT T2.fechaMas500, T2.mes, T2.AUTORES, Case when t2.mes = 4 then sum(T2.AUTORES) OVER(ORDER BY Convert(int, SUBSTRING(t2.fechaMas500, 0, 5)) asc, convert(int, t2.mes) ROWS BETWEEN unbounded preceding and current row) * 125 else IMPORTEPRIMA end as importeprima, sum(T2.AUTORES) OVER(ORDER BY Convert(int, SUBSTRING(t2.fechaMas500, 0, 5)) asc, convert(int, t2.mes) ROWS BETWEEN unbounded preceding and current row) AS TotalAcumulado, case when t2.mes = 0 then 2022 else case when t2.MES >= 4 then convert(int, SUBSTRING(t2.fechaMas500, 0, 5))+ 1 else convert(int, SUBSTRING(t2.fechaMas500, 0, 5)) end end as aniofiscal, t2.importeprima as ImporteprimaSinAcumulado from (
SELECT FECHAMAS500, MES,
CASE WHEN fechaMas500='2021-7' THEN T.AUTORES-4 ELSE AUTORES END AS AUTORES,
CASE WHEN T.MES=1 THEN T.AUTORES *60 ELSE
CASE WHEN T.MES=4 THEN T.AUTORES *125 ELSE
CASE WHEN T.MES=7 THEN
CASE WHEN fechaMas500='2021-7' THEN (T.AUTORES-4)*88 ELSE T.AUTORES *88 END ELSE
CASE WHEN T.MES=10 THEN T.AUTORES *60 ELSE
CASE WHEN T.MES=0 THEN T.AUTORES *125 END END END END END AS IMPORTEPRIMA
FROM(
select count(idautor) AS AUTORES , concat(year(dbo.getFechaAyudaSocial(IdAutor)),'-',month(dbo.getFechaAyudaSocial(IdAutor))) as fechaMas500, MONTH(dbo.getFechaAyudaSocial(IdAutor)) AS MES
from autores
where dbo.getFechaAyudaSocial(IdAutor)> '12/04/2021' AND IdEntidad='01' and FechaFallecimiento is null and FechaBaja is null and DATEDIFF(YEAR,FechaNac,GETDATE())<80 AND IDAUTOR NOT IN ('HED00002','AUT00067','AUT00088','AUT147673','AUT151254')
group by concat(year(dbo.getFechaAyudaSocial(IdAutor)),'-',month(dbo.getFechaAyudaSocial(IdAutor))), MONTH(dbo.getFechaAyudaSocial(IdAutor))
UNION
select count(idautor) AS AUTORES, '1990-0', 0 AS MES
from autores
where dbo.getFechaAyudaSocial(IdAutor)<= '12/04/2021' AND IdEntidad='01' and FechaFallecimiento is null and FechaBaja is null and IDAUTOR NOT IN ('AUT00453', 'AUT04569','AUT03502','HED00002')) T) T2) T3 order by Convert(int, SUBSTRING(fechaMas500, 0, 5)) asc, convert(int, mes) asc
Hi afrutos ,
Sorry I'm not very clear about your description, for instance the first one, “how many new members there are from 12/01/2022-1 to 12/04/2022-1 for April”, what's the relationship between April and the dates period, could you please give more concrete examples.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, I'm sorry finally I managed to do ir but usingSQL instead, with DAX I found it impossible,
I show you my function.
Maybe there is a possibility on DAX but I couldn't:
SELECT *, CASE WHEN t3.MES = 4 THEN IMPORTEPRIMA ELSE sum(T3.importeprima) OVER(PARTITION by t3.aniofiscal ORDER BY Convert(int, SUBSTRING(t3.fechaMas500, 0, 5)) asc, convert(int, t3.mes) ROWS BETWEEN unbounded preceding and current row) END as TotalAcumulado from (
SELECT T2.fechaMas500, T2.mes, T2.AUTORES, Case when t2.mes = 4 then sum(T2.AUTORES) OVER(ORDER BY Convert(int, SUBSTRING(t2.fechaMas500, 0, 5)) asc, convert(int, t2.mes) ROWS BETWEEN unbounded preceding and current row) * 125 else IMPORTEPRIMA end as importeprima, sum(T2.AUTORES) OVER(ORDER BY Convert(int, SUBSTRING(t2.fechaMas500, 0, 5)) asc, convert(int, t2.mes) ROWS BETWEEN unbounded preceding and current row) AS TotalAcumulado, case when t2.mes = 0 then 2022 else case when t2.MES >= 4 then convert(int, SUBSTRING(t2.fechaMas500, 0, 5))+ 1 else convert(int, SUBSTRING(t2.fechaMas500, 0, 5)) end end as aniofiscal, t2.importeprima as ImporteprimaSinAcumulado from (
SELECT FECHAMAS500, MES,
CASE WHEN fechaMas500='2021-7' THEN T.AUTORES-4 ELSE AUTORES END AS AUTORES,
CASE WHEN T.MES=1 THEN T.AUTORES *60 ELSE
CASE WHEN T.MES=4 THEN T.AUTORES *125 ELSE
CASE WHEN T.MES=7 THEN
CASE WHEN fechaMas500='2021-7' THEN (T.AUTORES-4)*88 ELSE T.AUTORES *88 END ELSE
CASE WHEN T.MES=10 THEN T.AUTORES *60 ELSE
CASE WHEN T.MES=0 THEN T.AUTORES *125 END END END END END AS IMPORTEPRIMA
FROM(
select count(idautor) AS AUTORES , concat(year(dbo.getFechaAyudaSocial(IdAutor)),'-',month(dbo.getFechaAyudaSocial(IdAutor))) as fechaMas500, MONTH(dbo.getFechaAyudaSocial(IdAutor)) AS MES
from autores
where dbo.getFechaAyudaSocial(IdAutor)> '12/04/2021' AND IdEntidad='01' and FechaFallecimiento is null and FechaBaja is null and DATEDIFF(YEAR,FechaNac,GETDATE())<80 AND IDAUTOR NOT IN ('HED00002','AUT00067','AUT00088','AUT147673','AUT151254')
group by concat(year(dbo.getFechaAyudaSocial(IdAutor)),'-',month(dbo.getFechaAyudaSocial(IdAutor))), MONTH(dbo.getFechaAyudaSocial(IdAutor))
UNION
select count(idautor) AS AUTORES, '1990-0', 0 AS MES
from autores
where dbo.getFechaAyudaSocial(IdAutor)<= '12/04/2021' AND IdEntidad='01' and FechaFallecimiento is null and FechaBaja is null and IDAUTOR NOT IN ('AUT00453', 'AUT04569','AUT03502','HED00002')) T) T2) T3 order by Convert(int, SUBSTRING(fechaMas500, 0, 5)) asc, convert(int, mes) asc
Hi afrutos ,
Glad you solved it!
Would you mind accept your reply as solution? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.
Best Regards,
Community Support Team _ kalyj