Current period vs. previous period WITHOUT date column

[corkemp]

Hi everyone,

I think this is relatively simple, but I haven’t been able to find the right solution for it. Any help would be greatly appreciated.

I have a table with school report data in it. Each student has a report in each subject several times a year. For each report, they get a number grade (called the attainment track). I want to create a measure that calculates the difference between the average of the most recent report period attainment track grade and the previous report cycle. I need to be able to use the measure in various contexts - e.g. the difference for a student across all their subjects, in each individual subject, for a subject as a whole and so. The report periods use a naming convention of 201718.1, 201718.2 etc. See the example below for a single student in a single subject.

I would also like the user to be able to choose which report cycles they want to compare - they select the first and last report cycles to compare. How might I go about doing this? I’m guessing I need two slicers, the selections of which are used in a measure…

Many thanks in advance,
Matt

[MK3010]

HI @corkemp

Could you please help to share the pbix file along with your desired output.

Thanks
Mukesh

[corkemp]

Hi Mukesh,

Please find attached a PBIX file which includes the required info. The output is in the screen shot (and also in the PBIX file), although here I have hard coded the report cycle names in the measures to illustrate what I am trying to achieve dynamically.

Current - previous example.pbix (51.7 KB)

Thank you.
Matt

[MK3010]

Hi @corkemp

You need to create 2 disconnected table from the main table.

Table 1–

1. Goto “Data” view and select “New Table”

2. Use below DAX to create new table with table name “SelectedRCy1”(you can change as per your choice)
   SelectedRCy1 = DISTINCT('Masked Report Data'[Report Cycle Name])

3. Rename the column as “Selection 1”

Table 2–

1. Goto “Data” view and select “New Table”

2. Use below DAX to create new table with table name “SelectedRCy2”(you can change as per your choice)
   SelectedRCy2 = DISTINCT('Masked Report Data'[Report Cycle Name])

3. Rename the column as “Selection 2”

You will have as below.

Make sure it is not connected to main table below data model FYR.

Create new measures as below.

First selected Period = 
CALCULATE ( 
    AVERAGEX ( 
        'Masked Report Data',
        'Masked Report Data'[Average of Attainment Track]
    ),
    FILTER ( 
        'Masked Report Data',
        [Report Cycle Name] = MIN(SelectedRCy1[Selection 1])
    )
)

Second selected Period = 
CALCULATE ( 
    AVERAGEX ( 
        'Masked Report Data',
        'Masked Report Data'[Average of Attainment Track]
    ),
    FILTER ( 
        'Masked Report Data',
        [Report Cycle Name] = MIN(SelectedRCy2[Selection 2])
    )
)

Difference = 
[First selected Period] - [Second selected Period]

Now you can create all the views. I can’t upload the pbix as using office system.

[corkemp]

Great - thank you so much! That works perfectly.

How would I go about creating a measure that calculates the average for the most recent report cycle minus the previous report cycle without having to make selections?

I’m thinking of using calculate where the filter is the Max of report cycle name minus Max-1. The max report cycle name measure is working, but Max - 1 isn’t returning the correct result.

Thanks.
Matt

[MK3010]

Hi @corkemp

You can use below DAX code to get 2nd latest item and then use this in your code. Better you add this as variable in the same measure and use the variable name where you want to get the value.

Second Latest = 
CALCULATE ( 
    MAX (
        [Report Cycle Name]
    ),
    ALL (
        'Masked Report Data'[Report Cycle Name]
        ),
        FILTER ( 
        'Masked Report Data',
        [Report Cycle Name] <> MAX([Report Cycle Name])
    )
) 

[corkemp]

Thank you for your help!
Matt