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.
Hi there,
I am trying to report on data dynamically by dates. I have a list of vendors and the Month/Year of their proposed Go Live. What I am trying to do is not report on anything in the past, and as time moves from one month to the next, keep dropping off Go Live dates in the past.
For example, 2 vendors went live in October 2019, 1 vendor is going live November 2019, 3 vendors are going live in December 2019, and 6 vendors are going live in January 2020.
I want to show a card for each month with the number of vendors going live that month, but excluding October as it is now November. In December, I want to exclude the October and November go lives. Is this possible?
Thanks in advance for your help!
Solved! Go to Solution.
Hi @Anonymous , ok, changed my table to be like yours, created a measure that counts all vendors in a month. Then used that in the measure that collects the number for this month.
Count Vendors = CALCULATE(COUNTROWS(Dates),ALLEXCEPT(Dates,Dates[Dates]))
My new table is Dates.
Current Month = MAXX(dates,if(month(Dates[Dates]) = MONTH(TODAY()),[Count Vendors]))
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
Hi @Anonymous ,
Try this:
Current month # of Vendors =
MAXX (
vendors,
IF ( MONTH ( vendors[Month] ) = MONTH ( TODAY () ), vendors[# of Vendors] )
)
My table is vendors
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
Hi Nathaniel,
Thanks for the quick reply. My data is stored differently. I do not have a count for each specific date, but a list of vendors with a date attached.
I was hoping to be able to get something that would count the number for each month/year. I am going to see if I can arrange my data to be more like your example and see if I can keep it up to date without having to do a lot of updating on my spreadsheets.
Thanks,
Marisa
Hi @Anonymous ,
Is this problem solved?
Best Regards,
Icey
Hi @Anonymous , ok, changed my table to be like yours, created a measure that counts all vendors in a month. Then used that in the measure that collects the number for this month.
Count Vendors = CALCULATE(COUNTROWS(Dates),ALLEXCEPT(Dates,Dates[Dates]))
My new table is Dates.
Current Month = MAXX(dates,if(month(Dates[Dates]) = MONTH(TODAY()),[Count Vendors]))
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
Thank you, this now works in my report! Your help is much appreciated!
Hi @Anonymous ,
You are very welcome!
Nathaniel
Proud to be a Super User!
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |