Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hi All,
I need to show the Total Value for each YEAR based on IF condition applied on the customer.
Example for 1 customer in current year:
If (Total Sales = 0 , Last Year Sales, Total Sales)
I have tried the following:
Solved! Go to Solution.
@Anonymous , Please find the file. I have corrected tmp_value. Refer Page 4
I created a new table for customer
@Anonymous , you can have
value_last_FY = CALCULATE(SUM(Sales[Value]),SAMEPERIODLASTYEAR('Date'[Date]))
or
value_last_FY = CALCULATE(SUM(Sales[Value]),dateadd('Date'[Date],-1,year))
use isblank
tmp_value = IF( isblank(SUM(Sales[Value])) , [value_last_FY] , SUM(Sales[Value]))
tmp_value = IF( isblank(SUM(Sales[Value])) || SUM(Sales[Value]) =0 , [value_last_FY] , SUM(Sales[Value]))
@amitchandak : Thanks for your response.
Please see the screen shot. total_value measure works well with Year -> Customer -> total_value
BUT, on the aggregated view the Total does not sum up correctly
@Anonymous , you have try like
total_value = SUMX(values('Date'[Date]), [tmp_value ])
total_value = SUMX(values('Date'[Year]), [tmp_value ]) // on second one if first one does work there
That is the row context. So the grand total is calculated again. This will force it take from row totals
@amitchandak : Somehow it is not working, but I think of the following issue.
In IF measure , I would like to consider prevoius year value of the same customer , ONLY when customer has NO order in current FULL year.
In the current scenario, it is picking prevoius year value based on the Date[Date]. It seems like something to do with SUMMARIZE
Any Idea?
@amitchandak I have uploaded a sample PBIX file. Can you please take a look .
https://drive.google.com/drive/folders/1E_k7VbgImoTft43JOncSMrwrWtCwVUVx?usp=sharing
The correct Estimate_Value measure should be total of tmp_value measure per year. (Total at year level is not correct).
FY Year starts from 01st Sept. Below is the desired result .
2018 - 18558377
2019 - 22658571
2020 - 25084241
2021 - 29162252
What am I missing here ? Thanks a lot for your support.
@Anonymous , Please find the file. I have corrected tmp_value. Refer Page 4
I created a new table for customer
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.