How to filter and multiply the month

[talex1]

Hi,

I am trying to build a report and want to select a date period in my visual filter.
Lets say Jan 2023 - May 2023 and I have a table that shows the monthly payment for a is 500€ and for b 650€.

Now I just want that my bar chart shows 2500€ for a and 3250€ for b.

I dont want to have redundancies and a single row for each month in my table, because than it works.
I just want to have a one row as that value doesn't change and the report should just multiply that value for each month that
has been selected in the filter.

Can not find any way to do this

[talex1]

I have tried this as an example:

Measure = 1000 * 1 + (DATEDIFF(min('Datumstabelle'[Date]),max('Datumstabelle'[Date]),MONTH))

It shows me the exact number of months. For instance 01.01.2022 - 14.08.2023 20 Months,
therefore 20.000. But if I select 15.08.2023 the report shows 48.000 ‌‌
But it should remain 20.000. I don't get it...

[talex1]

I can post anything correct here, I get the message it is html

[Ritaf1983]

Hi @talex1 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[talex1]

Payment	Start contract	Type	Apartment	Name	Rent	additional costs	End contract
1	01.01.2023	Business	A	xxx	500,00 €	110,00 €	31.12.9999
2	01.02.2023	Private	B	yyy	650,00 €	100,00 €	31.12.9999

Hi @Ritaf1983

Payment 1 and 2 are due every month. If I select the date Jan 2023 - May 2023 in the report I would like to see 2500€ (Rent x 5) for Apartment A and 2600€ (Rent x 4)  for Apartment B for the example above.

So I want the report to multiply that value for each month that has been selected.

[Ritaf1983 · Accepted solution]

Hi @talex1 

Suppose you have a date table connected to the data.
Here is a DAX you can use:

rent For selected pereiod = sum('Table'[Rent]) * DATEDIFF(min('Calendar'[Date]),max('Calendar'[Date]),MONTH)

Link to a sample file 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[talex1]

I have tried this as an example:

Measure = 1000 * 1 + (DATEDIFF(min('Datumstabelle'[Date]),max('Datumstabelle'[Date]),MONTH))<div> <div><span>It shows me the exact number of months. For instance 01.01.2022 - 14.08.2023 20 Months,<div><span>therefore 20.000. But if I select 15.08.2023 the report shows 48.000 <li-emoji id="lia_thinking-face" src="/html/@FA29672578C228A5801C692E46FA7E67/emoticons/1f914.png" class="lia-deferred-image lia-image-emoji" alt=":thinking_face:" title=":thinking_face:"><div><span>But it should remain 20.000. I don't get it...</span></div></li-emoji></span></div></span></div></div>

[talex1]

Okay now this works:

Measure = 1000 * (1 +  (DATEDIFF(min('Datumstabelle'[Date]),max('Datumstabelle'[Date]),MONTH)))