Billed All Months

[pritshet]

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 stores data in the Same table "sales" .Store Code SC1 & SC2. If you se StoreCode SC1 its is belled all months .i.e. last 12 Months from the filterdate but for StorceCode SC2 it has not been billed in first couple of Months, highlighted in grey above for SC2 

If I select Dec 2022, then need to check if the entries are there for all the past 12 months. If all entries in past 12 months then SelectColum StoreCode in my DAX

Any help would be great. Need the DAX for the same.

Regards,

Pritam Shetty

[andhiii079845]

Hi @pritshet ,

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])

[pritshet]

Thanks!! implementing it and trying to test it. Keep you posted.

[andhiii079845]

I do not understand the logic: 

1) if one store has datas for all month, choose is store. 

2) what if both has data for all month?

3) no store has data for all month? 

1) is clear, but 2) and 3)?

[pritshet]

Please find my answers inline 

2) what if both has data for all month? PS: Then Show both the Stores. In brief i want to show Stores that have billed all months.

3) no store has data for all month?  PS: then i donot show that Stores in the report.

[andhiii079845]

Can you please confirm that only this sales table (no relationship to other tables) is involved with the three columns (YYYMMDD, StoreCode and Billno.) ? I will try to build a PBI for you.

[andhiii079845]

Hi @pritshet ,

i try to find a solution. See this PBI File Billed All Months.pbix

Please check the PBI File. There has to be no connection between both tables. 

The measure:

Measure = 
VAR _selectdate = SELECTEDVALUE('calendar'[Date])
VAR _tb = SUMMARIZE('calendar','calendar'[Date],"_26monthpriv",dateadd('calendar'[Date],-26,MONTH))
RETURN CALCULATE(sum(sale[Sale]),FILTER(sale,sale[BillDate]>=maxx(_tb,[_26monthpriv]) && sale[BillDate]<=_selectdate))

[pritshet]

Thanks!! for help, I have edited my content above. I don't need sales for the last 12 months, I need to check if the particular store has made an entry each and every month for the last 12 months

[Greg_Deckler]

@pritshet Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[pritshet]

Edited, hope this time its better