Need help with reporting data by dynamic dates.

[Anonymous]

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!

[Nathaniel_C]

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

[Anonymous]

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

[Icey]

Hi @Anonymous ,

Is this problem solved?

Best Regards,

Icey

[Nathaniel_C · 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]))

 
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

[Anonymous]

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

[Nathaniel_C]

Hi @Anonymous ,
You are very welcome!

Nathaniel