Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
TimSchaeffer
New Member

how to calculate percentages between two months (increase rate)

Hello,

 

i am having trouble with calculating the increase rate between two months. i have counted tickets for each year/month(yyyy:mm). this is how i calculate the amount of tickets for each month:

 

Aantal Verzoeken per Maand =
CALCULATE(
    COUNT('tweedelijns melding'[Index]),
    ALLEXCEPT('tweedelijns melding', 'tweedelijns melding'[MaandJaar])
)
 
i want to make a table that shows the total amount for each month (that is currently working), but also the amount of tickets of the previous month, and what the increase/decrease rate is. I tried the following formula, but this doesn't work:
_______________________________________________________________________
Vorige Maand Verzoeken =
CALCULATE(
    [Aantal Verzoeken per Maand],
    DATEADD('tweedelijns melding'[Aanmelddatum], -1, MONTH)
 
and i tried:
 
Vorige Maand Verzoeken =
CALCULATE(
[Aantal Verzoeken per Maand],
PREVIOUSMONTH('tweedelijns melding'[Aanmelddatum])
)
_______________________________________________________________________
)
 
these formulas show the same amount of tickets as the current month
TimSchaeffer_0-1718814683787.png

Can someone tell me how i can calculate this correctly? and just to be sure, is this calculation for the increase/decrease percentages correct? 

Percentage Verandering =
DIVIDE(
    [Aantal Verzoeken per Maand] - [Vorige Maand Verzoeken],
    [Vorige Maand Verzoeken],
    0
)
 
thanks in advance!
 
Kind regards, Tim Schaeffer
1 ACCEPTED SOLUTION

@TimSchaeffer,

 

It appears that the date field in the main table is DateTime. This needs to be a Date (no time portion) so it will join properly to the date table. If the main table doesn't have a date field, you can add a column that removes the time portion (Power Query or DAX).

 

The visual needs to use the Year-Month field in the date table, not the main (fact) table.

 

It's recommended to use unidirectional relationships, not bidirectional. Note the two arrows on the relationship between the tables. There should be one arrow pointing from the date table to the main table.





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

Proud to be a Super User!




View solution in original post

4 REPLIES 4
DataInsights
Super User
Super User

@TimSchaeffer,

 

Does your model have a date table? If not, it's recommended to create a date table using either Power Query or DAX. Once you create a date table, mark it as a date table, create a relationship to the main table, and use the date table in time intelligence functions such as PREVIOUSMONTH. Also use date table fields in your visual. Example measures:

 

Aantal Verzoeken per Maand =
COUNT ( 'tweedelijns melding'[Index] )
Vorige Maand Verzoeken =
CALCULATE ( [Aantal Verzoeken per Maand], PREVIOUSMONTH ( 'Date'[Date] ) )

 

Your percentage measure looks correct.





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

Proud to be a Super User!




TimSchaeffer_0-1718871878727.png

i checked the data, i see the data is not correctly converted aswell

 

also i got the table wrong, the count does work but just not the data model / count previous month. i added these columns, and it shows the amount for each month, but not previous or percentages.

TimSchaeffer_0-1718879213847.png

 

@TimSchaeffer,

 

It appears that the date field in the main table is DateTime. This needs to be a Date (no time portion) so it will join properly to the date table. If the main table doesn't have a date field, you can add a column that removes the time portion (Power Query or DAX).

 

The visual needs to use the Year-Month field in the date table, not the main (fact) table.

 

It's recommended to use unidirectional relationships, not bidirectional. Note the two arrows on the relationship between the tables. There should be one arrow pointing from the date table to the main table.





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

Proud to be a Super User!




Thank you for your answer! i created the data table in the model and it looks like this:

TimSchaeffer_0-1718868089128.png

The relation is for ''aanmelddatum'' to the date table, with many to 1. 

 

i tried to use the count function in ''tweedelijns melding'' but this just counts the total and does not filter the data for each month.  

 

Aantal Verzoeken per Maand V2 =
COUNT('tweedelijns melding'[Index])
 
 

what should i do to filter it for each year&month?

 
 
 

 

 

 

 

 

 

 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.