Need Help with Dax formula to pull Use percentage of last month only

[dbrandone]

I have hit a wall in figuring out the correct dax formula to get the last month's use percentage to show up through a measure. I need to pull a list of the item numbers(unique identifiers) that were created last month, then take those numbers and based on whether they were used or not, determine a percentage that were used. I have the item number and created date in one table. I then have the item number and whether the item was used or not used in another table. I thought determining the list of the items created first as a variable would be best, but I can summarize columns, but getting the correct formula to take the current date and then subtract one month and then pull a list of the items with the item numbers for that month. From there I would use that variable to use on the second table and then divide the two numbers, not used versus used. 

I have been waiting for that aha moment with dax to where I can read and write proficiently in it and the format makes sense, but it has not happened yet. Hopefully soon!! any tips or explanations would be greatly appreciated.

[DataInsights]

@dbrandone,

Would you be able to provide sample data or a link to your pbix?

[dbrandone]

So below is an example of the data I have in two seperate tables. I need to pull the ID numbers that are tied to the previous month(In this example, lets say that the previous month is February) from Table 1 and then take their outcome from table 2 and find the Used percentage. I can quite easily take the outcome and put it in the same table as 1 and that is what I did today. I know SQL code very well, but DAX is kicking my butt as I cant wrap my head around to format and foundation of its thinking.

Table 1		Table 2
UniqueIdentifier	Item Made on		Unique Identifier	Outcome
1	1/3/2021		1	Used
2	1/5/2021		2	Used
3	1/7/2021		3	NotUsed
4	1/9/2021		4	Used
5	1/11/2021		5	NotUsed
6	1/13/2021		6	NotUsed
7	1/15/2021		7	NotUsed
8	1/17/2021		8	Used
9	1/19/2021		9	Used
10	1/21/2021		10	Used
11	1/23/2021		11	Used
12	1/25/2021		12	Used
13	1/27/2021		13	NotUsed
14	1/29/2021		14	NotUsed
15	1/31/2021		15	Used
16	2/2/2021		16	Used
17	2/4/2021		17	Used
18	2/6/2021		18	NotUsed
19	2/8/2021		19	NotUsed
20	2/10/2021		20	Used
21	2/12/2021		21	Used
22	2/14/2021		22	Used
23	2/16/2021		23	NotUsed
24	2/18/2021		24	NotUsed
25	2/20/2021		25	Used
26	2/22/2021		26	Used
27	2/24/2021		27	Used
28	2/26/2021		28	NotUsed
29	2/28/2021		29	NotUsed
30	3/2/2021		30	Used
31	3/4/2021		31	Used

[DataInsights]

@dbrandone,

Try this solution.

1. Create data model with a date table:

2. Create measures:

Count Item = COUNT ( Table1[Unique Identifier] )

Percent Used =
VAR vNumerator =
    CALCULATE ( [Count Item], Table2[Outcome] = "Used" )
VAR vDenominator = [Count Item]
VAR vResult =
    DIVIDE ( vNumerator, vDenominator )
RETURN
    vResult

3. Result:

You can add a date slicer to filter the visual by the selected Month Year.

[dbrandone]

This is great. Thanks.

If I wanted to put a card on the dashboard that is titled "Used % last month" or even "Used % in the last 30 days", that is something that I have struggled with. Now that I have the "vResult", would it just be:

                                Previous Month Used % = PreviousMonth(vResult)

[DataInsights]

@dbrandone,

How are you defining "last month"? Is it specified by the user in a slicer, or based on today's date?

[dbrandone]

Today's Date.

I have wrestled with this as to either just show the previous month. For example, today is 7/1, so today would be the first day the card would show data from 6/1-6/30.

or

Should I do the used % of the last 30 days and it is constantly moving. Leaning towards the previous month from a data use need.

[DataInsights · Accepted solution]

@dbrandone,

This measure is based on today's date:

Percent Used Last Month = 
VAR vToday =
    TODAY ()
VAR vEndDate =
    EOMONTH ( vToday, -1 )
VAR vStartDate =
    EOMONTH ( vToday, -2 ) + 1
VAR vNumerator =
    CALCULATE (
        [Count Item],
        Table2[Outcome] = "Used",
        DATESBETWEEN ( DimDate[Date], vStartDate, vEndDate )
    )
VAR vDenominator =
    CALCULATE ( [Count Item], DATESBETWEEN ( DimDate[Date], vStartDate, vEndDate ) )
VAR vResult =
    DIVIDE ( vNumerator, vDenominator )
RETURN
    vResult

[dbrandone]

Give me a second to give you a data example. We deal with very sensitive data so I will see what we can do