Find items exist last month but not this month.

[MsTess2020]

Hello,

Can someone help me on how to get items that exist last month but not this month? I need the counts of items deleted.

Here is my scenario:

1) if items exist in the previous month (ACT_FL = 1, see sample in yellow) but the current month (ACT_FL = 0, see sample in blue), that means the items as been deleted.

2) if items exist in the previous month (ACT_FL = 1, see sample in yellow) but do not exist in the current month, it means items are deleted. 

Below is the SQL code I use to get the result, but I don't know how to put it in Power BI Dax.

; with LastMonth as (

SELECT DISTINCT FAC.WHSECD AS WHSE_CD, INV.STK_ITM, INV.DEPT_CD, INV.ACT_FL

FROM LUMIS_III.dbo.vwActiveFacWarehouse FAC
INNER JOIN DHSSCMDB.dbo.tblR_INVN_MOArchives INV ON FAC.WHSECD = INV.WHSE_CD
WHERE INV.ACT_FL = 1 
and FAC.Facility_CD = @deptCd
AND MONTH(INV.END_DATE) = MONTH(DATEADD(m, -1, CONVERT(varchar, @curMonth) ))
AND YEAR(INV.END_DATE) = YEAR(DATEADD(m, -1, CONVERT(varchar, @curMonth) ))

)

,ThisMonth as (

SELECT DISTINCT FAC.WHSECD AS WHSE_CD, INV.STK_ITM, INV.DEPT_CD, INV.ACT_FL
FROM LUMIS_III.dbo.vwActiveFacWarehouse FAC
INNER JOIN DHSSCMDB.dbo.tblR_INVN_MOArchives INV ON FAC.WHSECD = INV.WHSE_CD
WHERE INV.ACT_FL = 1 
and FAC.Facility_CD = @deptCd
AND MONTH(INV.END_DATE) = MONTH( CONVERT(varchar, @curMonth) )
AND YEAR(INV.END_DATE) = YEAR( CONVERT(varchar, @curMonth) )

)

SELECT a.ACT_FL, a.DEPT_CD, a.STK_ITM, a.WHSE_CD, b.STK_ITM, b.WHSE_CD, b.ACT_FL
FROM LastMonth a
LEFT JOIN ThisMonth b ON a.WHSE_CD + a.STK_ITM = b.WHSE_CD + b.STK_ITM
WHERE (b.WHSE_CD + b.STK_ITM) IS NULL OR (a.ACT_FL = 1 AND b.ACT_FL = 0)

Let said if I select the date for '2/28/2021', the previous date will be '1/31/2021'

thank you in advance. 

Isabelle

[v-yetao1-msft]

Hi @MsTess2020 

(1)As for the problem that you mentioned that the current table and previous table do not take effect, you did not add measure1 and measure2 to the visual of the current table and previous table. The measure will only take effect when it is placed in the visual.

Like this :

(2)From my pbix file , you can see that I filter the date by a measure and slicer with field 'Date'[Date] . I didn't create any relationships between these tables . So my slicer is effective .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MsTess2020 · Accepted solution]

Hi Ailsa,

After i have play around, I substitute my data with your sample, and it seems to give me the correct results. That is if I don't connect it with my date table. But I need to connect it with my date table for all my visuals to work together. So can you please give me some suggestions as to how I can link it with my current date table?
Here is my test and the delete and new values are correct. 

As soon as I connect it with my date table, it will gives me blank. So please, tell me what is the issue. 

Thank you.

[MsTess2020]

Hi Ailsa,

Your suggestion is working now. I don't have to connect to the Date table because the other table already connects to the Date table. So everything is working now. Thank you so much for all your help. 

[MsTess2020]

Yes, I forget to add the measure in my visual. But when I added the measure, the previous month shows nothing. Is there a way I can send you my .pbix file? Can you give me your email since I cannot attach it here?

I even tried to use use 'current month'[End_Date 2]= EOMONTH(MAX('Date'[Date]),-1 but nothing is working for me.

Thanks again for helping me. 

[v-yetao1-msft]

Hi @MsTess2020 

Maybe you can see the filter condition I used is the time interval , you can use 'current month'[End_Date 2]= EOMONTH(MAX('Date'[Date]),-1 to replace .

measure 2 = CALCULATE(COUNT('previous month'[End_Date 2]),FILTER('previous month','previous month'[End_Date 2] = EOMONTH(MAX('Date'[Date]),-1)))

Best Regards

Community Support Team _ Ailsa Tao

[v-yetao1-msft]

Hi @MsTess2020 

According to the data you provided, I created a sample .

Original data (Data table):

(1)Create two new table with the value from current month and previous month .

current month = SELECTCOLUMNS('Data table',"WHSE_CD 1",'Data table'[WHSE_CD],"STK_ITM 1",'Data table'[STK_ITM],"End_Date 1",'Data table'[End_Date])

previous month = SELECTCOLUMNS('Data table',"WHSE_CD 2",'Data table'[WHSE_CD],"STK_ITM 2",'Data table'[STK_ITM],"End_Date 2",'Data table'[End_Date])

(2)Then create a Date table with the column 'Data table'[End_Date]

Date = SELECTCOLUMNS('Data table',"Date",'Data table'[End_Date])

(3)Create measures(through 'Date'[Date] to return values from current month and previous month) for two new tables .

measure 1 = CALCULATE(COUNT('current month'[End_Date 1]),FILTER('current month','current month'[End_Date 1]=MAX('Date'[Date])))

measure 2 = CALCULATE(COUNT('previous month'[End_Date 2]),FILTER('previous month','previous month'[End_Date 2]<MAX('Date'[Date]) && 'previous month'[End_Date 2]>EOMONTH(MAX('Date'[Date]),-2)))

(4)Add a slicer with field 'Date'[Date]

The result is as shown :

(5) Through the above steps, we have obtained two tables respectively, one is the returned data of the current month, and the other is the data of the previous month .Then we need create measures to return the numbers of delete values and new values between the two tables .

delete value =
VAR t1 =
    SUMMARIZE (
        FILTER (
            'current month',
            'current month'[End_Date 1] = SELECTEDVALUE ( 'Date'[Date] )
        ),
        'current month'[WHSE_CD 1],
        'current month'[STK_ITM 1]
    )
VAR t2 =
    SUMMARIZE (
        FILTER (
            'previous month',
            'previous month'[End_Date 2] = EOMONTH ( SELECTEDVALUE ( 'Date'[Date] ), -1 )
        ),
        'previous month'[WHSE_CD 2],
        'previous month'[STK_ITM 2]
    )
VAR tab =
    EXCEPT ( t2, t1 )
RETURN
    COUNTX ( tab, [STK_ITM 2] )

new value =
VAR t1 =
    SUMMARIZE (
        FILTER (
            'current month',
            'current month'[End_Date 1] = SELECTEDVALUE ( 'Date'[Date] )
        ),
        'current month'[WHSE_CD 1],
        'current month'[STK_ITM 1]
    )
VAR t2 =
    SUMMARIZE (
        FILTER (
            'previous month',
            'previous month'[End_Date 2] = EOMONTH ( SELECTEDVALUE ( 'Date'[Date] ), -1 )
        ),
        'previous month'[WHSE_CD 2],
        'previous month'[STK_ITM 2]
    )
VAR tab =
    EXCEPT ( t1, t2 )
RETURN
    COUNTX ( tab, [STK_ITM 1] )

The final result is as shown :

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MsTess2020]

Hi Alisa-msft,

Thank you so much for helping me. but I am still unable to get the result. Please help.

I tried your suggestion but somehow, I still get nothing. 

As you can see from the screen (Previous data), it still did not show last month. and deleted value at the bottom shows nothing.

Below is what i have created from your sample:

1) current month data 

2) Previous month data

3) My Date table. But i have another table that connects to this date table to use for the slicer.

Period table to use for the slicer.

4) measure for current data

5) measure for previous data

6) measure for delete items

Here is my relationship. Please note that both previous and current data get from INVN_All.

Would you please tell me what I did wrong? Thanks again for your help.

[MsTess2020]

One more thing, can you please explain why the following measure where EOMONTH is -2 not -1?

measure 2 = CALCULATE(COUNT('previous month'[End_Date 2]),FILTER('previous month','previous month'[End_Date 2]<MAX('Date'[Date]) && 'previous month'[End_Date 2]>EOMONTH(MAX('Date'[Date]),-2)))

[v-yetao1-msft]

Hi @MsTess2020 

Could you provide your sample and expected result ? These SQL commands alone are not very helpful .

Best Regards

Community Support Team _ Ailsa Tao

[MsTess2020]

Hello, sorry for the late reply.  I don't see there is a way to attached a sample here. Any way, I have a table like this. with detail and date.

I want to create two table, one contain last month and the other for this monht. for example, let say i selected 4/30/2021. so this month is 4/30/2021 and last month will be 3/31/2021. I have sample table below. as you can see, the one i highligh in 'green' is deleted because it does not exist any more on 4/30/2021 and the one I highligh in 'yellow' is the new items because it does not exist before.  I just one the count. so deleted item will equal to 1, and new items will equal to 4. this is the result i want. thank you.

[Ashish_Mathur]

Hi,

Posting an SQL statement will not help.  Show a simple dataset, desscribe the question and show the expected result.

[MsTess2020]

Can anyone out there help me, please? I need your expertise. I only have one table that uses the Date field to calculate last month's items and compare them with current month items to get the deleted items. If you can help me put the following SQL script into DAX format, that will significantly appreciate.

SELECT a.WHSE_CD, CASE WHEN a.WHSE_CD IS NULL THEN 0 ELSE COUNT(a.WHSE_CD) END as Counts
FROM Table1 a
LEFT JOIN Table2 b ON a.WHSE_CD + a.STK_ITM = b.WHSE_CD + b.STK_ITM
WHERE (b.WHSE_CD + b.STK_ITM) IS NULL OR (a.ACT_FL = 1 AND b.ACT_FL = 0)
GROUP BY a.WHSE_CD

[ebeery]

@MsTess2020 I'm not sure I fully understand the tables in the screenshots you provided, but from your description this sounds to me like a variation on the "New and Returning Customers" pattern from Dax Patterns (SQLBI).

I would recommend having a look at that pattern as a starting point.

https://www.daxpatterns.com/new-and-returning-customers/

[MsTess2020]

Thank you, but I have looked at the one you suggested and still do not understand. I have tried many different things, but I can't get it to work. For example, how do you compare two tables where the other table fields are blank in DAX? See highlight in yellow below?