Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have purchasing data that is added to every month with a monthly time stamp added. In January, all I have is Jan data. In February, I stack Feb under the Jan data, and so forth. I would like to figure out a way that isolates items that are purchased every month. My initial though would be to do a measure that counts each item, and then I can filter to the number of times it shows up (in August, filter to 8). How would this be written in DAX? Is there a better way to do it?
Thanks!
Solved! Go to Solution.
@Anonymous
Try the following steps
1. Create a measure called NumberofMonthsLapsed
NumberofMonthsLapsed = Month(MAx([MonthYear])) - Month(MIn([MonthYear])) + 1
This will generate the number of months between the first date and lastdate in the Sheet1 table.
2. Create a summary table using
SumCount = SUMMARIZE ( Sheet1,Sheet1[Item],"CountProd",Countrows(Sheet1))
3. Create a column in the SumCount table ShowYes as
ShowYes = If([CountProd]=[NumberofMonthsLapsed],1,0)
If the CountProd against each item is same as the NumberofMonthsLapsed set ShowYes as 1 else set it as 0.
4. Create a Table Report with fields from SumCount Table- Item, CountProd
5. In the VisualFilter Drag the column ShowYes and set Show items when the value is 1
6. The output will be
If this solves your issue, please accept it as solution and also give KUDOS.
Cheers
CheenuSing
Can you provide sample data?
It sounds like you could create a custom column like:
MONTH([Date])
Then you could just do a count aggregation or a measure that uses COUNT or COUNTROWS along with that custom column in the axis.
I am a tad confused how I would go about a count aggregation or a measure using COUNT or COUNTROWS. I'm fairly green with DAX and Power BI, so please bear with me. I have a link to sample data below. Note that items A, C, & D are bought every month, while B & D are not. My goal would be to easily filter out A, C, & D in a report page.
@Anonymous
Try the following steps
1. Create a measure called NumberofMonthsLapsed
NumberofMonthsLapsed = Month(MAx([MonthYear])) - Month(MIn([MonthYear])) + 1
This will generate the number of months between the first date and lastdate in the Sheet1 table.
2. Create a summary table using
SumCount = SUMMARIZE ( Sheet1,Sheet1[Item],"CountProd",Countrows(Sheet1))
3. Create a column in the SumCount table ShowYes as
ShowYes = If([CountProd]=[NumberofMonthsLapsed],1,0)
If the CountProd against each item is same as the NumberofMonthsLapsed set ShowYes as 1 else set it as 0.
4. Create a Table Report with fields from SumCount Table- Item, CountProd
5. In the VisualFilter Drag the column ShowYes and set Show items when the value is 1
6. The output will be
If this solves your issue, please accept it as solution and also give KUDOS.
Cheers
CheenuSing
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |