topic Billed All Months in DAX Commands and Tips

Billed All Months

Anonymous — Mon, 13 Mar 2023 11:59:16 GMT

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

Re: Billed All Months

Greg_Deckler — Mon, 13 Mar 2023 10:29:17 GMT

Anonymous 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.

Re: Billed All Months

andhiii079845 — Mon, 13 Mar 2023 10:56:45 GMT

Hi Anonymous ,

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

Re: Billed All Months

Anonymous — Mon, 13 Mar 2023 12:00:53 GMT

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

Re: Billed All Months

Anonymous — Mon, 13 Mar 2023 12:01:15 GMT

Edited, hope this time its better

Re: Billed All Months

andhiii079845 — Mon, 13 Mar 2023 17:30:47 GMT

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

Re: Billed All Months

Anonymous — Tue, 14 Mar 2023 08:23:08 GMT

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.

Re: Billed All Months

andhiii079845 — Tue, 14 Mar 2023 08:28:09 GMT

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.

Re: Billed All Months

andhiii079845 — Tue, 14 Mar 2023 10:26:56 GMT

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

Re: Billed All Months

Anonymous — Thu, 23 Mar 2023 06:05:44 GMT

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