Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Need help with reporting data by dynamic dates.

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!

 

1 ACCEPTED 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]))

 


vend12.PNG

 



vend1.PNG

 

 
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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Nathaniel_C
Super User
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
vend.PNG

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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. Capture.PNG

 

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

Icey
Community Support
Community Support

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

 


vend12.PNG

 



vend1.PNG

 

 
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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Thank you, this now works in my report! Your help is much appreciated!

Hi @Anonymous ,
You are very welcome!

Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.