If I have the following data (and a date table connected on the date column):
I want to display on a card the number of people who appeared more than once last month. Therefore Mary, Daniel and Cathy appeared more than once in February therefore the output should be 3.
My thought is that I need to summarize the table virtually, grouping by year, month and name, counting appearances but I also think I am probably trying to overcomplicate things.
Solved! Go to Solution.
You may download the revised PBI file from here.
Hope this helps.
Thanks, very similar to my effort but the way you have gotten the date is a little more elegant.
I have a question though... you have chosen to use the ADDCOLUMNS function to count the occurrences rather than adding it as part of the SUMMARIZE function. The following appears to provide the same result:
is their a reason why doing this via ADDCOLUMNS is better? or is it simply an alternative?
Thank you, one final question if you don't mind... on line 10 you are referring to the 'Count' measure you created in step one. Is this simply for clarity or it more advantageous compared with replacing your [Count] with COUNT[Person], having the calculation all within one measure?
Edit: actually changing it no longer gives me the correct answer...
So I think I have a solution but I am sure there is a better way to do this with time intelligence, variables used to ensure I am selecting the right month from the right year:
var _previousmonth = MONTH(EOMONTH(TODAY(), -2)+1)
var _year = IF(_previousmonth = 12, YEAR(TODAY())-1, YEAR(TODAY()))
COUNTROWS(FILTER(SUMMARIZECOLUMNS('Date'[Year], 'Date'[MonthOfYear], Table[Person], "@Count", COUNT(Table[Appearance])), [@Count] > 1 && [Year] = _year && [MonthOfYear] = _previousmonth )
Can anyone point out a flaw in the above or a better way to do this with time intelligence? (or just a better way)
@brownrice , First create a measure like this with help form date table and TI
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
Then a measure like
Countx(values(Table[Person]), if([Last Month Sales]>1, [Person], Blank())
Sorry I'm not really following this, I have the following:
I want to display on a card the number of people who appeared more than once last month. Therefore Mary, Daniel and Cathy appeared more than once in February therefore the output today, in March, should be 3. The card should update each month, so in February the card would read 2 as Colin and Bob both appeared more than once in January.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.