cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MsTess2020
Frequent Visitor

Find items exist last month but not this month.

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'

MsTess2020_0-1626913738904.png

 

thank you in advance. 

Isabelle

 

 

 

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

MsTess2020_0-1627583357318.png

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.

View solution in original post

14 REPLIES 14
Ailsa-msft
Community Support
Community Support

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 :

Ailsamsft_0-1627544840756.png

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

Ailsamsft_1-1627544840759.png

Ailsamsft_2-1627544840763.png

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.

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. 

MsTess2020_0-1627583357318.png

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.

View solution in original post

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. 

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. 

Ailsa-msft
Community Support
Community Support

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

Ailsa-msft
Community Support
Community Support

Hi @MsTess2020 

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

Original data (Data table):

Ailsamsft_0-1627463907108.png

(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 :

Ailsamsft_1-1627463907111.pngAilsamsft_2-1627463907115.png

(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 :

Ailsamsft_3-1627463907118.pngAilsamsft_4-1627463907120.png

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.

 

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. 

MsTess2020_0-1627487424108.png

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 

MsTess2020_1-1627487691652.png

2) Previous month data

MsTess2020_2-1627487735422.png

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

MsTess2020_3-1627487780398.png

Period table to use for the slicer.

MsTess2020_7-1627488294223.png

 

4) measure for current data

MsTess2020_4-1627487829538.png

5) measure for previous data

MsTess2020_5-1627487878637.png

6) measure for delete items

MsTess2020_6-1627487949431.png

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

MsTess2020_8-1627488562672.png

 

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

 

 

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

 

Ailsa-msft
Community Support
Community Support

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

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.

MsTess2020_0-1627398715650.png

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.

MsTess2020_1-1627399151969.png

 

 

Ashish_Mathur
Super User III
Super User III

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
MsTess2020
Frequent Visitor

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
Solution Supplier
Solution Supplier

@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/

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?

 

MsTess2020_0-1626968442595.png

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors