Running Total?!

[shaykoooo]

Hi,

I am trying to build a DAX expression to help me check, for each of the following months- 10/2017 through 09/2018 if there was sales ofat least one USD in the previos 12 months. For example, for 10/2017, the expression checks month 10/2016 till 09/2017. Also, the the formula shold check if there are sales on the month evaluated (10/2017 in this example).

TY!

[v-juanli-msft · Accepted solution]

Hi @shaykoooo

Try measures as below

previos 12 months =
CALCULATE (
    SUM ( Sheet3[sale] ),
    FILTER (
        ALL ( Sheet3 ),
        DATEDIFF ( [date], MAX ( [date] ), MONTH ) >= 1
            && DATEDIFF ( [date], MAX ( [date] ), MONTH ) <= 12
    )
)

check if sales currentmonth = IF(MAX([sale])>0,1,0)

formula to check = IF([check if sales currentmonth]>0||[previos 12 months]>0,"has slaes")

 

Please note that, since i don't know what exactly about your dataset, i use "year/month/1" to refer to each month of a year.

If you have multiple dates for each month, you could sum sales for each month, then apply with measures above.

Best Regards

maggie