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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Comparing values to previous months

Hi,

 

I am wondering whether it is possible to achieve this and display as a table.

hetal247_0-1671528592941.png

I have a table that displays client number by month. I then want to add new measures that calculates the difference to Apr of that year, difference to last month, 6 months and 12 months ago as shown above.

 

I have created a new measure that calculates if a date falls in one of the above categories and flag is if true. I then add a new measure that calculates the difference such as for 12 months ago:

Change to 12mths Ago = (CALCULATE(DISTINCTCOUNT('PBI all'[Service user ID]),'PBI all'[ReportingRow]="Yes",'PBI all'[Current Month flag]="Y")-CALCULATE(DISTINCTCOUNT('PBI all'[Service user ID]),'PBI all'[ReportingRow]="Yes",'PBI all'[12 Months Ago Flag]="Y"))
 
Now this works if i just have these measures as a separate table but i would like to display all as one table as shown above.
 
Is this acheivable in any other way?
 
kind regards
 
Hetal
1 ACCEPTED SOLUTION

Please see the attached sample file.  You would not need to create a measure for every month; just for the relative month.


jennratten_0-1671823805452.png

 

View solution in original post

7 REPLIES 7
jennratten
Super User
Super User

Hello - yes, it is possible to recreate the values shown in your screensnip.  It would be best if you have a separate date table that is related to your data table.  Then you would have the separate measures that reference the dates in the date table.  Below are sample scripts for measures along with the url for the associated DAX pattern, which includes detailed explanations and scripts for a date table that you can just copy and paste into your project.  For the scripts below, just replace the table and column references with the applicable table and column names for your project.  For each measure, you can change the number of months to offset from current, which is shown as -1 (for prior month) in the example. 

 

Add a column to your table 'Year Month Number' to reference in your .  This will be in the format of yyyyMM and will be for the date of the current row (not the date for any previous period).

https://www.daxpatterns.com/month-related-calculations/ 

 

# Users

Users :=
DISTINCTCOUNT('PBI all'[Service user ID])

Prior Month

Users PM :=
VAR CurrentYearMonthNumber = SELECTEDVALUE ( 'Date'[Year Month Number] )
VAR PreviousYearMonthNumber = CurrentYearMonthNumber - 1
VAR Result =
    CALCULATE (
        [Users],
        REMOVEFILTERS ( 'Date' ),
        'Date'[Year Month Number] = PreviousYearMonthNumber
    )
RETURN
    Result

 

Anonymous
Not applicable

Hi @jennratten,

 

I have tried your method and still not getting the result i want. I have created a new date table from the link you have provided and then created a new measure below:

 

Users PM =
var CurrentYearMonthNumber = SELECTEDVALUE('HP Date'[Year Month Number])
var PreviousYearMonthNumber = CurrentYearMonthNumber -1
var Result =
    CALCULATE(
        Measuretbl[CountuserV1],
        REMOVEFILTERS('HP Date'),
        'HP Date'[Year Month Number]=PreviousYearMonthNumber
    )
return
    Result
 
This is giving me the figure for previous month but i want to display my table as my screensnip. I want to show the numbers by month up to the current month. Then within the same table after the current month, show the difference in numbers to previous month, since Apr 22, 6 months ago and 12 months ago.
 
Do i need to add a new measure that calculates the difference? also what objects do i show in my table?
 
Not sure if i have understood your answer clearly so would be grateful if you can provide an example of how you set out your table
 
My table is set out as below:
 
hetal247_0-1671621233897.png 

 

SUG, calendarMonths.DisplayPeriod objects are from my data table
CountUserV1 is from my measurestable (distinctcount of User IDs)
Users PM is my new measure i have created and linked to my Date Table
 
kind regards
 
Hetal

In order to have all of this shown on one table, you will need to either create measures for all of the values to be included in the table or create a calculated table that produces the desired results and then add all to the visual.  Calculations should be dynamic so you won't have to update them for specific dates, like CurrentValue-1Mo, CurrentValue-2Mo, CurrentValue-3Mo, ChangeVs12Mo, ChangeVs6Mo, etc.  

Please let me know if I can further assist.

Anonymous
Not applicable

Just to confirm, do you mean i would need to create measures for each month, Apr, May, Jun etc as well as diff to Apr, diff to previous months, 6 months and 12 months?

 

Are you able to give me an example?

 

kind regards

 

Hetal

Please see the attached sample file.  You would not need to create a measure for every month; just for the relative month.


jennratten_0-1671823805452.png

 

Anonymous
Not applicable

Thank you very much for this. 

amitchandak
Super User
Super User

@Anonymous , create your measure and then with help from time intelligence have measure like

 

Year behind Sales = CALCULATE([Measure],dateadd('Date'[Date],-1,Year))

 

6 Months behind Sales = CALCULATE([Measure],dateadd('Date'[Date],-6,Month))

 

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.