Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I want to have rolling 12 months on Sum and Count measure.
I have seen many post related to this, but nothing worked well with my scenario-
here is my scenario -
I have field as =
This is my Datefield =Period("MMM-YYYY")
Sum(sales)
Count({<Sale= {'External'} >} ExternalSale)
So now i need to have rolling 12 months(From this October 2019 to October 2018) in the above sum and Count measure.
what is the best way ?
Thanks in advance!
Finally with some trail and error method i came up with right fix.
Please find the script below-
SUM({< Period = {">=$(=Date(MonthStart(Today(),-12),'MMM-YYYY'))<=$(=Date(MonthStart(Today()),'MMM-YYYY'))"} >}[Sales})
Count({<Sale= {'External'}, Period = {">=$(=Date(MonthStart(Today(),-12),'MMM-YYYY'))<=$(=Date(MonthStart(Today()),'MMM-YYYY'))"} >} ExternalSale)
Thanks to myself! 🙂
Finally with some trail and error method i came up with right fix.
Please find the script below-
SUM({< Period = {">=$(=Date(MonthStart(Today(),-12),'MMM-YYYY'))<=$(=Date(MonthStart(Today()),'MMM-YYYY'))"} >}[Sales})
Count({<Sale= {'External'}, Period = {">=$(=Date(MonthStart(Today(),-12),'MMM-YYYY'))<=$(=Date(MonthStart(Today()),'MMM-YYYY'))"} >} ExternalSale)
Thanks to myself! 🙂