Report showing future months

[vahrameev]

Hi

I'm new to Power Bi so please have mercy 🙂

My Power Bi is connected to Dynamics 365 Sales.

I'm trying to count total number of Leadid and every month should include data from all previous months.

In my case it looks like this:

Count of leadid running total in Month = 
CALCULATE(
	COUNTA('leads'[leadid]),
	FILTER(
		CALCULATETABLE(
			SUMMARIZE(
				'leads',
				'leads'[createdon].[MonthNo],
				'leads'[createdon].[Month]
			),
			ALLSELECTED('leads')
		),
		ISONORAFTER(
			'leads'[createdon].[MonthNo], MAX('leads'[createdon].[MonthNo]), DESC,
			'leads'[createdon].[Month], MAX('leads'[createdon].[Month]), DESC
		)
	)
)

I don't know why but future months are also displayed in this case.

Is there an option to exclude these future months?

[vahrameev]

Hi @v-kelly-msft 

Thank you very much for your answer!

This IF is exactly what I was missing.

[amitchandak]

@vahrameev , I case you have date in table try with a date table

Cumm Sales = CALCULATE(COUNTA('leads'[leadid]),filter(allselected(date),date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(COUNTA('leads'[leadid]),filter(allselected(date),date[date] <=max(Sales[Sales Date])))

[vahrameev]

@amitchandak  thank you very much for your reply

my dates are in the [createdon] field in Dynamics

but like I've said before I'm new to Power Bi so please be more specific where I should use your formulas

thank you

[v-kelly-msft]

Hi @vahrameev ,

Could you pls upload your .pbix file to onedrive business and share the link with us?Remember to remove the confidential information.

Better attach the correct result.

Best Regards,
Kelly

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

[vahrameev]

Hi @v-kelly-msft 

Thank you for trying to help

This is an example of what I'm working with (extracted data from Dynamics to excel to show only necessary fields) https://1drv.ms/u/s!ApMveMy2oLCRhJoUC3B0UnISzmskxg?e=K2R9vb

I would like this report to show number of Leads created this month plus all he Leads created before (like it is showing now) but also not to display future months.

[v-kelly-msft · Accepted solution]

Hi @vahrameev ,

Sorry for the late reply!

Modify your measure as below:

Count of LeadID running total in Month = 
IF(MAX('Sheet1'[Created On].[MonthNo])<=MONTH(TODAY())&&YEAR(MAX('Sheet1'[Created On]))<=YEAR(TODAY()),
CALCULATE(
	COUNTA('Sheet1'[LeadID]),
	FILTER(
		CALCULATETABLE(
			SUMMARIZE(
				'Sheet1',
				'Sheet1'[Created On].[MonthNo],
				'Sheet1'[Created On].[Month]
			),
			ALLSELECTED('Sheet1')
		),
		ISONORAFTER(
			'Sheet1'[Created On].[MonthNo], MAX('Sheet1'[Created On].[MonthNo]), DESC,
			'Sheet1'[Created On].[Month], MAX('Sheet1'[Created On].[Month]), DESC
		)
	)
),
BLANK())

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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