Forum Discussion
Return Value based on customer max sales date = data max refresh date
Hi,
Hoping someone can help me, I have a scenario where I receive Country data at different times of the month. I take the last value per Country and Product to be the Total Sales amount for that month.
So as an example:
Data for Belgium and Norway has last updated on 30 September 2020 & Denmark in Oct 2020.
The first thing I noticed creating a Latest Sum measure, was the Latest Sum Total was showing the latest value for the latest date per customer. So I added a conditional measure if Last Customer Sales period does not equal Country Refresh Date then show 0.
When I switch customers in my slicer I notice that another customer who has a 0 Amount value in the latest Month (i.e. Oct 2020) the Total Sales also shows as zero. This is not correct and should show the Sep 2020 figure as this is the last value for Norway's data.
Anyone know how I can solve this. Basically, I need to return the amount based on customer max sales date = data max refresh date. My pbix and workings is in the link below;
https://drive.google.com/file/d/1p3kNea7sCzR33Ge7h0QN2Lk_2DdVmkYn/view?usp=sharing
av9 , You have to try formula like
Sales = sumx(VALUES(Country[Country]) , IF([Country Refresh Date]=[Last Country Refresh Date], [Latest SUM], BLANK())) Sum Amount = CALCULATE ( SUMX (values(Country[Country]),Sum(Sales[Amount])), FILTER ( Sales, (MAX(Sales[Date]) = [Country Refresh Date] ))) Total Sales = sumx(VALUES(Country[Country]) ,IF(ISBLANK([Last Sales Period for Customer]),BLANK(),IF(ISBLANK([Sales]),0,[Sales])))
2 Replies
- amitchandak
Super User
av9 , You have to try formula like
Sales = sumx(VALUES(Country[Country]) , IF([Country Refresh Date]=[Last Country Refresh Date], [Latest SUM], BLANK())) Sum Amount = CALCULATE ( SUMX (values(Country[Country]),Sum(Sales[Amount])), FILTER ( Sales, (MAX(Sales[Date]) = [Country Refresh Date] ))) Total Sales = sumx(VALUES(Country[Country]) ,IF(ISBLANK([Last Sales Period for Customer]),BLANK(),IF(ISBLANK([Sales]),0,[Sales]))) - av9
Helper III
Fantastic, that worked !!!😀