Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Counting stores where sales within date range

HI,

I have stores that sale items and I need to find last sale per store within selected (calendar) date range. Sale date is related to calendar date. I want to get one row per StoreID with latest sale date that is older then max selected calendar range, and then tto count those rows. I can't solve this, help is appriciated. Thanks 

This is what I have so far.

TEST := 
VAR MaxCalDateSelected=MAX(Calendar[DAte])
RETURN
CALCULATE( COUNTROWS( Sales,
FILTER(Sales,Sales[StoreID] &&
Sales[TotalPaymentsRunningTotal]>0 &&  
MAX(Sales[TotalPaymentsRunningTotal] &&  
MAX(Sales[SaleDate]<=MaxCalDateSelected)
)
) 

 

Need Help

2 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    hi, Anonymous

    Please share your sample pbix file or some data sample and expected output.Do mask sensitive data before uploading.

     

     

    Best Regards,

    Lin

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    I solved this, solution is to create Summary table using SUMMARIZE, like 

    SUMMARIZE(tblSales, [StoreID], CALCULATE(MAX(Saledate),Sales[TotalPaymentsRunningTotal]>0))