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 , you can have
value_last_FY = CALCULATE(SUM(Sales[Value]),SAMEPERIODLASTYEAR('Date'[Date]))
value_last_FY = CALCULATE(SUM(Sales[Value]),dateadd('Date'[Date],-1,year))
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
@amitchandak I have uploaded a sample PBIX file. Can you please take a look .
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.
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.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.