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'.   S...
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)))

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.