Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm using the following measure to calculate the growth of this fiscal year period (e.g. april to sept) Vs LY same period but if LFY is 0 the result is not correct as the formula apply the growth respect to another FY
example:
FY2020 sales 100
FY2019 sales 0
FY2018 sales 90
DAX result +11% (100/90-1)
Here the measure I'm using:
Any Idea?
Thanks
Solved! Go to Solution.
Hi @SoniaRadaelli ,,
Try the following measure below:
% Vs. LYS =
VAR first_date =
CALCULATE ( ( MIN ( SALESSHARED[anno FYS] ) ), ALL ( SALESSHARED[anno FYS] ) )
VAR lastdatenonblank =
FILTER(ALL(SALESSHARED[anno FYS]), SALESSHARED[anno FYS] < MAX(SALESSHARED[anno FYS]))
RETURN
IF (
MAX ( SALESSHARED[anno FYS] ) = first_date,
BLANK (),
DIVIDE (
SUM ( SALESSHARED[NETTO] ),
CALCULATE (
SUM ( SALESSHARED[NETTO] ),
FILTER (
ALL ( SALESSHARED[anno FYS] ),
SALESSHARED[anno FYS] = MAXX ( lastdatenonblank, SALESSHARED[anno FYS] )
)
)
) - 1
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @SoniaRadaelli ,
Try the following measure:
% Vs. LY orig =
VAR first_date =
CALCULATE ( (MIN( sales_totale[anno FY] )), ALL ( sales_totale[anno FY] ) )
VAR lastdatenonblank =
FILTER (
SUMMARIZE (
ALL ( sales_totale[anno FY], sales_totale[Sales] ),
sales_totale[anno FY],
"@sales", SUM ( sales_totale[Sales] )
),
[@sales] > 0
&& sales_totale[anno FY] < MAX ( sales_totale[anno FY] )
)
RETURN
IF(MAX(sales_totale[anno FY]) = first_date,
DIVIDE (
SUM ( sales_totale[Sales] ),
CALCULATE (
SUM ( sales_totale[Sales] ),
FILTER (
ALL ( sales_totale[anno FY] ),
sales_totale[anno FY] = MAXX ( lastdatenonblank, sales_totale[anno FY] )
)
)
) - 1)
In your case since you were making only the selewction of the values below the max year selection the value you returned was 2019 value and not 2018.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
here the result using your dax
🤔
modifying your if statement, adding the underlined part:
I have the following result
as you can see in FY2020 the % is calculated respect to FY2018 and not on FY2019
Hi @SoniaRadaelli ,,
Try the following measure below:
% Vs. LYS =
VAR first_date =
CALCULATE ( ( MIN ( SALESSHARED[anno FYS] ) ), ALL ( SALESSHARED[anno FYS] ) )
VAR lastdatenonblank =
FILTER(ALL(SALESSHARED[anno FYS]), SALESSHARED[anno FYS] < MAX(SALESSHARED[anno FYS]))
RETURN
IF (
MAX ( SALESSHARED[anno FYS] ) = first_date,
BLANK (),
DIVIDE (
SUM ( SALESSHARED[NETTO] ),
CALCULATE (
SUM ( SALESSHARED[NETTO] ),
FILTER (
ALL ( SALESSHARED[anno FYS] ),
SALESSHARED[anno FYS] = MAXX ( lastdatenonblank, SALESSHARED[anno FYS] )
)
)
) - 1
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
69 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |