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
Grazmeister
Regular Visitor

How to show data divided by future months

Hello everyone, 

I usually use this community to find help and ideas for my Power BI dashboards but it's my first time posting, since I cannot get around a problem. 

WHAT I HAVE:

I have data as shown below. 

I have the first column "Entrada Data" and "Saída Data", then two calculated columns:
1. "MesesAvenças" which calculate the months elapsed between "Entrada Data" and "Saída Data", the DAX expression is:

MesesAvenças =
IF(
NOT(
ISBLANK(
'Conceitos Cobrados'[Fita N.º])
),
IF(
'Conceitos Cobrados'[Conceito] = "Renovação de Avençado",
IF(
DATEDIFF(
'Conceitos Cobrados'[Entrada Data],
'Conceitos Cobrados'[Saída Data],
MONTH
) < 0,
0,
DATEDIFF(
'Conceitos Cobrados'[Entrada Data],
'Conceitos Cobrados'[Saída Data],
MONTH
)
),
-500
),
-1000
)

2. "ValorMes" which simply DIVIDES the amount for that line (a column that is missing from the image below) by "MesesAvenças"

 

Grazmeister_0-1638816667715.png

 

I also have a DATE TABLE created which spans all days from 01/01/2019 to 31/12/2022 which has a 1:* relationship to the table above (which has data that only spans from 01/01/2019 until 30/11/2021

 

WHAT I WANT

Take has an example the last line. 
We have:

VALUEENTRADA DATASAÍDA DATAMESESAVENÇASVALORMES
21701/06/202101/11/2021543,4

 

As you can see, someone paid 217 for something that starts on the 01/06/2021 and spans until 01/11/2021 which is about 5 months

So that brings the value per month to around 43,40.

 

So, what i want is to show the value paid per month, in a visual, distributed per month, like so:

VALUETOTALJUNEJULYAUGUSTSEPTEMBEROCTOBER
21743,443,443,443,443,4

 

 

Since i also have costumers that paid this month (NOVEMBER 2021) for 365 days, for example, that spans for future dates aswell. 

 

Could you guys provide some insight on how to achieve this? 

I can provide more data if needed. 

 

Thanks in advance!

 

1 ACCEPTED SOLUTION
5 REPLIES 5
Grazmeister
Regular Visitor

@amitchandak to provide better understanding I want something like this:

Grazmeister_2-1638880685694.png

You can see the 600€ line divided by month (Janeiro = 01/2022, Fevereiro = 02/2022, etc)

 

Thanks, @amitchandak !

Your solution worked!

I needed to add an Index column so it could treat each line distinctively and needed to change the comparison of the end date (Saída Data) to "<" instead of "<=". 

 

The formula which worked is this:

Value by day of Month =
CALCULATE(
SUMX(
SUMMARIZE(
FILTER(
CROSSJOIN(
'Conceitos Cobrados',
'DATAS_fitas'
),
'DATAS_fitas'[Date] >= 'Conceitos Cobrados'[Entrada Data] && 'DATAS_fitas'[Date] < 'Conceitos Cobrados'[Saída Data]
),
'Conceitos Cobrados'[Index],
'DATAS_fitas'[Date],
'DATAS_fitas'[YearMonthnumber],
'Conceitos Cobrados'[Valor],
'Conceitos Cobrados'[Entrada Data],
'Conceitos Cobrados'[Saída Data]
),
DIVIDE(
'Conceitos Cobrados'[Valor],
DATEDIFF(
'Conceitos Cobrados'[Entrada Data],
'Conceitos Cobrados'[Saída Data],
DAY
)
)
)
)
 
Thanks for your help!

I got to play around your post a bit and managed to get the 600€ as this:

Grazmeister_3-1638881264103.png

So, maybe it's just the days that are messing this up. 

 

Thanks in advance.

 

Hello @amitchandak , thanks for stopping by. 

Well, I followed the post you provided but i sadly doesn't meet what I need, or I didn't manage to get it properly working. 

So after I adapted your formulas I end up getting this result:

Grazmeister_0-1638879559855.png

 

And the data I have is this:

Grazmeister_1-1638879613844.png

 

As you can see, I need to show a visual with Month/Year (your first example) but with the amount paid divided by the duration of the stay. 
As an example:
1. First line shows €1.941,00 paid in 11/2021 that's valid until the beggining of 12/2021 (this amount goes into the 11/2021 pot);

2. The second line likewise;

3. Third line, €2.835,40 paid in 12/2021 that's valid until the beggining of 01/2022 (this amount goes into the 12/2021 pot) 

4. And so on.

 

5. The problem is the 5th line: someone paid 600€ for 12 months, starting in 01/2022 and ending at the beggining of 01/2023. 

I need to show 50€ (=600/12) every month since it was paid (50€ in 01/2022, 50€ in 02/2022, etc.).

Maybe i'm missing something? 
(I deleted the relationship between the DATE table and the FACT table since it messed the crossjoin DAX you so kindly provided.)

 

Thanks!

 

 

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.