Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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.
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
107 | |
68 | |
48 | |
44 | |
43 |