Forum Discussion
Get right measure
Hi everyone,
I need to make a calculation as follows, and I'm having problems, I need a matrix where in the columns, are the months APRIL, JULY, OCTOBER, JANUARY
- NO. MEMBERS:
For APRIL how many new members there are from 12/01/YEAR(TODAY())-1to 12/04/YEAR(TODAY())-1,
For JULY how many new members there are from 12/04/YEAR(TODAY())-1 through 12/07/ YEAR(TODAY())-1
For OCTOBER how many new members from 12/07/YEAR(TODAY())-1 through 12/10/YEAR(TODAY())-1
For JANUARY how many new members from 12/10/YEAR(TODAY())-1 through 12/04/YEAR(TODAY())-1
Can this be done in one function?
-IMPORT =
column members April * 125
column members July * 80
column members October * 60
column members January * 60
can this be done in another function?
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
3 Replies
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 _ kalyjIf 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) ascHi 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
