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.
Hi,
I have a measure in DAX that tells me the number of customers who have bought less than the previous year.
The problem is that looks like Power Bi not take into account those customers who have not bought this year. But if a client bought the previous year AND not this year, it should also be considered.
this is my DAX code:
DECREASE =
COUNTROWS(
FILTER(
SUMMARIZECOLUMNS(
EMPRES[NAME],
CLIENT[CODCLI]
,
"TYTD",
CALCULATE( SUM ( CLIENT[IMPORT] ),
DATESYTD(DimDate2[DateFull]))
,
"LYTYTD",
CALCULATE( SUM ( CLIENT[IMPORT] ),
SAMEPERIODLASTYEAR(DimDate2[DateFull])
)
),
[TYTD]<[LYTYTD]
)
)
Client is table with the code of the client ([codcli]), the date of the bought (related with the calendar table DimDate2), and the amount ([import]).
In the report i have a slicer with the year, when i select the present year(2021) it says 571 clients meet the condition , but this is not correct.
The strangest thing is that if I try the same formula In DAX Studio it does return the correct number of 934. The difference is that in DAX Studio i put manually the year:
EVALUATE
{
COUNTROWS (
FILTER (
SUMMARIZECOLUMNS (
EMPRES[NAME],
CLI[CODCLI],
"TYTD",
CALCULATE (
SUM ( CLIENT[IMPORT] ),
FILTER ( CLIENT, YEAR ( CLIENT[fecalb] ) = 2021 )
),
"LYTYTD",
CALCULATE (
SUM ( CLIENT[IMPORT] ),
FILTER ( CLIENT, YEAR ( CLIENT[fecalb] ) = 2020 )
)
),
[TYTD] < [LYTYTD]
&& EMPRES[NAME] = "DMI"
)
)
}
Solved! Go to Solution.
Hi @Ion_Ander
From your statement, I know if you use DATESYTD/SAMEPERIODLASTYEAR function to get TYTD and LYTYTD will get wrong result. However you will get correct result by specific year value.
It is better for you to build a unrelated slicer table and try selectedvalue function in your dax code.
Firstly build a unrelated Year table. And you will build a slicer by year column in it.
DECREASE =
VAR _SELECTYEAR = SELECTEDVALUE(SLICER[Year])
RETURN
COUNTROWS (
FILTER (
SUMMARIZECOLUMNS (
EMPRES[NAME],
CLI[CODCLI],
"TYTD",
CALCULATE (
SUM ( CLIENT[IMPORT] ),
FILTER ( CLIENT, YEAR ( CLIENT[fecalb] ) = _SELECTYEAR )
),
"LYTYTD",
CALCULATE (
SUM ( CLIENT[IMPORT] ),
FILTER ( CLIENT, YEAR ( CLIENT[fecalb] ) = _SELECTYEAR-1 )
)
),
[TYTD] < [LYTYTD]
&& EMPRES[NAME] = "DMI"
)
)
If this reply still couldn't solve your problem, please share a sample with me by your Onedrive for Business.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Ion_Ander
From your statement, I know if you use DATESYTD/SAMEPERIODLASTYEAR function to get TYTD and LYTYTD will get wrong result. However you will get correct result by specific year value.
It is better for you to build a unrelated slicer table and try selectedvalue function in your dax code.
Firstly build a unrelated Year table. And you will build a slicer by year column in it.
DECREASE =
VAR _SELECTYEAR = SELECTEDVALUE(SLICER[Year])
RETURN
COUNTROWS (
FILTER (
SUMMARIZECOLUMNS (
EMPRES[NAME],
CLI[CODCLI],
"TYTD",
CALCULATE (
SUM ( CLIENT[IMPORT] ),
FILTER ( CLIENT, YEAR ( CLIENT[fecalb] ) = _SELECTYEAR )
),
"LYTYTD",
CALCULATE (
SUM ( CLIENT[IMPORT] ),
FILTER ( CLIENT, YEAR ( CLIENT[fecalb] ) = _SELECTYEAR-1 )
)
),
[TYTD] < [LYTYTD]
&& EMPRES[NAME] = "DMI"
)
)
If this reply still couldn't solve your problem, please share a sample with me by your Onedrive for Business.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks @v-rzhou-msft your solution works fine.......... but now i have a new problem, perhaps you can help.
If i try to put this measure into a matrix or into a chart it give me error, after investigating i see that SUMMARIZECOLUMN is not compatible with matix and charts.
so, is it possible to do the same with SUMMARIZE and ADDCOLUMNS? i never use the summarize previously because it is supposed to be discouraged and not very sure about how to use with multiple tables, i supose i need addcolumn....... I´m a bit lost.
I share my Power Bi where yo can see the error of the matrix:
https://a31069321-my.sharepoint.com/:u:/g/personal/ionander_gabyl_com/Eag3xi-Q20dDk5RQsuoXR8QBv7iA6J...
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |