Forum Discussion

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

Calculate date value for date before MAX date

Hi, In my model I have sales data that is populated for each country at different times of the month. Therefore I can have data for Country 'Belgium' as at '31 Oct' but Norway will be '30 Sep'.

 

So I use the last sales date to calculate total sales for each country; 

Country Refresh Date = CALCULATE(MAX(Sales[Date]), REMOVEFILTERS(Customer[Customer]))
 
Sum Sales Amount =
CALCULATE
( SUMX(VALUES(Country[Country]),SUM(Sales[Amount])),FILTER(Sales, (MAX(Sales[Date])=[Country Refresh Date])))
 

If I want to see the previous months sales data, how do I calculate the prev month based on the date of the last Country Refresh Date per country?

 

Attached is the PBIX I am using.

https://drive.google.com/file/d/1OvSLP05PXI7ZGEkwYC5KQFJ_9qhbQMaq/view?usp=sharing

 

 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    av9 , Try measures like


    Sum Sales Amount =
    CALCULATE
    ( SUMX(VALUES(Country[Country]),SUM(Sales[Amount])),FILTER(Sales,Sales[Date]=CALCULATE(max(Sales[Date]),ALLEXCEPT(Country,Country[Country]))))

    Last month Sales Amount =
    CALCULATE
    ( SUMX(VALUES(Country[Country]),SUM(Sales[Amount])),FILTER(Sales,Sales[Date]=eomonth(CALCULATE(max(Sales[Date]),ALLEXCEPT(Country,Country[Country])),-1)))

     

    ////or

    Sum Sales Amount =
    CALCULATE
    ( SUMX(VALUES(Country[Country]),SUM(Sales[Amount])),FILTER(Sales,eomonth(Sales[Date],0)=CALCULATE(max(Sales[Date]),ALLEXCEPT(Country,Country[Country]))))

    Last month Sales Amount =
    CALCULATE
    ( SUMX(VALUES(Country[Country]),SUM(Sales[Amount])),FILTER(Sales,eomonth(Sales[Date],0)=eomonth(CALCULATE(max(Sales[Date]),ALLEXCEPT(Country,Country[Country])),-1)))

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    av9 , Try measures like


    Sum Sales Amount =
    CALCULATE
    ( SUMX(VALUES(Country[Country]),SUM(Sales[Amount])),FILTER(Sales,Sales[Date]=CALCULATE(max(Sales[Date]),ALLEXCEPT(Country,Country[Country]))))

    Last month Sales Amount =
    CALCULATE
    ( SUMX(VALUES(Country[Country]),SUM(Sales[Amount])),FILTER(Sales,Sales[Date]=eomonth(CALCULATE(max(Sales[Date]),ALLEXCEPT(Country,Country[Country])),-1)))

     

    ////or

    Sum Sales Amount =
    CALCULATE
    ( SUMX(VALUES(Country[Country]),SUM(Sales[Amount])),FILTER(Sales,eomonth(Sales[Date],0)=CALCULATE(max(Sales[Date]),ALLEXCEPT(Country,Country[Country]))))

    Last month Sales Amount =
    CALCULATE
    ( SUMX(VALUES(Country[Country]),SUM(Sales[Amount])),FILTER(Sales,eomonth(Sales[Date],0)=eomonth(CALCULATE(max(Sales[Date]),ALLEXCEPT(Country,Country[Country])),-1)))

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

      The measures didn't quite work as expected, but it did give the idea to use Eomnth to calculate the previous month from max date.