cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bievemar Frequent Visitor
Frequent Visitor

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

Accepted Solutions
Highlighted
Nathaniel_C Super Contributor
Super Contributor

Re: Need help with reporting data by dynamic dates.

Hi @bievemar , 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

View solution in original post

6 REPLIES 6
Nathaniel_C Super Contributor
Super Contributor

Re: Need help with reporting data by dynamic dates.

Hi @bievemar ,
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

 

 

bievemar Frequent Visitor
Frequent Visitor

Re: Need help with reporting data by dynamic dates.

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

Highlighted
Nathaniel_C Super Contributor
Super Contributor

Re: Need help with reporting data by dynamic dates.

Hi @bievemar , 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

View solution in original post

Community Support Team
Community Support Team

Re: Need help with reporting data by dynamic dates.

Hi @bievemar ,

Is this problem solved?

 

Best Regards,

Icey

bievemar Frequent Visitor
Frequent Visitor

Re: Need help with reporting data by dynamic dates.

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

Nathaniel_C Super Contributor
Super Contributor

Re: Need help with reporting data by dynamic dates.

Hi @bievemar ,
You are very welcome!

Nathaniel

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)