Forum Discussion

av9's avatar
av9
Icon for Helper III rankHelper III
5 years ago
Solved

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

  • 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])))