The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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:
Can someone tell me how i can calculate this correctly? and just to be sure, is this calculation for the increase/decrease percentages correct?
Solved! Go to Solution.
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.
Proud to be a Super User!
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.
Proud to be a Super User!
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.
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.
Proud to be a Super User!
Thank you for your answer! i created the data table in the model and it looks like this:
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.
what should i do to filter it for each year&month?
User | Count |
---|---|
44 | |
28 | |
25 | |
22 | |
22 |
User | Count |
---|---|
83 | |
48 | |
30 | |
26 | |
20 |