Forum Discussion
Billed All Months
Hi Team, We have a requirement to check if a Store has Billed Consecutively All months for the last 12 Months. I have data in the below format. If you see the above , i have 2...
andhiii079845
Solution Sage
Solution SageHi Anonymous ,
I build a new PBI File. Perhaps i make it to complicated. Billed All Months Rev2.pbix
I create a separate calendarslicer (with the endofmonth) and a store dim table.
The main part is this measure:
I make it dynamic that you can choose with _lookupmonth how long you want to look back.
I build a table with all end of month dates and do than a crossjoin with the store table to get a table of all stores and end of month dates. Than I check for every billdate (end of month) and store if there was a sale and count it.
Later you can show this measure with the store table in one matrix. You can use the measure also as a filter if you want.
new =
VAR _lookupmonth = -12
VAR _selectdate = SELECTEDVALUE(('calendarslicer'[endofmonth]))
VAR _tb = SUMMARIZE('calendarslicer','calendarslicer'[Date],"_monthpriv",ENDOFMONTH(dateadd('calendarslicer'[Date],_lookupmonth,MONTH)))
VAR _table12month = SUMMARIZE(FILTER(ALL('calendar'),'calendar'[endofmonth]<=SELECTEDVALUE('calendarslicer'[endofmonth]) && 'calendar'[endofmonth]>=maxx(_tb,[_monthpriv])),'calendar'[endofmonth])
VAR _store = SUMMARIZE(store,store[store])
VAR _cross = CROSSJOIN(_store,_table12month)
VAR _tablesale = ADDCOLUMNS(_cross,"_Count",
VAR _date = 'calendar'[endofmonth]
VAR _store = store[store]
Var _result = COUNTX(FILTER(ALL(sale),sale[billdateendofmonth]=_date && sale[Storecode] = _store),1) RETURN _result
)
VAR _nosale = SUMMARIZE(store,store[store],"_countofmonthwithoutsale",ABS(_lookupmonth)-CALCULATE(COUNTX(_tablesale,[_Count]),FILTER(_tablesale,_store=store[store])))
RETURN maxx(_nosale,[_countofmonthwithoutsale])
Thanks!! implementing it and trying to test it. Keep you posted.