Forum Discussion

vazmor's avatar
vazmor
Icon for Helper II rankHelper II
6 years ago

Same Store Last Year Like for Like

Heloo,

I have problems to make the metric of the Sames Store Like for like, I have a measure that calculates very close but it is not the final result that I am looking for as you can see in the image according to the selection, someone can help me. Thanks in advance.

Attached file

File Test 

 

Dax

 

Sales Value LY LFL = 
VAR CustomersInvoices = 
    ADDCOLUMNS (
        VALUES ( Fac_Sales[ID_LFL] ), -- Use Customers table if you have one
         "AmountCurrentMonth", [Sales Value],
        "AmountPreviousMonth", CALCULATE ( [Sales Value],CROSSFILTER('Calendar'[Date],'dDate'[Date],OneWay),SAMEPERIODLASTYEAR(dDate[Date]) )
    )
VAR CustomersWithGrowth =
    FILTER ( CustomersInvoices, [ID_LFL] =[ID_LFL] )
VAR Result =
  SUMX ( CustomersWithGrowth,[AmountPreviousMonth])
RETURN Result

 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI vazmor,

     

    What is the expected output? Can you describe in more detail what the problem is?

     

    Best Regards // Ulf

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

    In you formula I see a calendar date and date date. In case you need last year you can use same calendar .

    I use datesytd or totalytd 

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

     

  • vazmor's avatar
    vazmor
    Icon for Helper II rankHelper II
    6 years ago
    Hi, try what you put amitchandak, but it didn't work. Anonymous what I need is to calculate for example, when I select New Door I calculatelast year of those stores that you select for the current year but ignore the New Door filter since in the last year those stores may have been transfer. The results I hope to get are the ones you see in the image and are by selection type.
     

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

      Not on the same page. But you can use ytds as the filter of each other

      Continue store =calculate([YTD Sales],filter(Sales,[Last YTD Sales]>0 || not(isblank([Last YTD Sales]))))

new  store =calculate([YTD Sales],filter(Sales,[Last YTD Sales]=0 || isblank([Last YTD Sales])))
      • vazmor's avatar
        vazmor
        Icon for Helper II rankHelper II
        6 years ago

        Hello, I tried what I have been put but I still do not get to the results of the image, the file is attached to do tests, thanks.