need dynamic measure

[vjnvinod]

Hi Team,

below is my current measure

TER = IF(SELECTEDVALUE('Account Listing'[Quarter])="Q2",CALCULATE([Gterrevenue],'Account Listing'[Quarter]="Q2")-CALCULATE([Gterrevenue],'Account Listing'[Quarter]="Q1"),IF(SELECTEDVALUE('Account Listing'[Quarter])="Q1",CALCULATE([Gterrevenue],'Account Listing'[Quarter]="Q1"), IF(SELECTEDVALUE('Account Listing'[Quarter])="Q3",CALCULATE([Gterrevenue],'Account Listing'[Quarter]="Q3")-CALCULATE([Gterrevenue],'Account Listing'[Quarter]="Q2"),CALCULATE([Gterrevenue],'Account Listing'[Quarter]="P02"))))

the issue is that this measure is not dynamic & I need to change the highlighted red parameter every month.

is there any resolution to this?

[Anonymous]

Hi

In what range should the dynamic part be? I.e. P01, P02,..., P12?

If this is the case then you should be able to do the following:

TER =
VAR __monthlyValidation = "P" & IF(MONTH(TODAY())<10;"0";"") & MONTH(TODAY())
RETURN
IF (
    SELECTEDVALUE ( 'Account Listing'[Quarter] ) = "Q2",
    CALCULATE ( [Gterrevenue], 'Account Listing'[Quarter] = "Q2" )
        - CALCULATE ( [Gterrevenue], 'Account Listing'[Quarter] = "Q1" ),
    IF (
        SELECTEDVALUE ( 'Account Listing'[Quarter] ) = "Q1",
        CALCULATE ( [Gterrevenue], 'Account Listing'[Quarter] = "Q1" ),
        IF (
            SELECTEDVALUE ( 'Account Listing'[Quarter] ) = "Q3",
            CALCULATE ( [Gterrevenue], 'Account Listing'[Quarter] = "Q3" )
                - CALCULATE ( [Gterrevenue], 'Account Listing'[Quarter] = "Q2" ),
            CALCULATE ( [Gterrevenue], 'Account Listing'[Quarter] = __monthlyValidation )
        )
    )
)

 Ps. it is a good idea to put your code through a DAX formatter to make it more readable (i.e. https://www.daxformatter.com/ )

If the above helps then please mark it as the solution, kudos is also appreciated.

[vjnvinod]

@Anonymous 

range order is something like this

P01, P02, Q1, P04, P05,Q2, P07, P08, Q3, P10, P11, P12

Let me know

[Anonymous]

Then you can probably change the measure to:

VAR __month =
    MONTH ( TODAY () )
VAR __variable =
    IF (
        __month IN { 3, 6, 9 },
        "Q" & ROUNDUP ( __month / 3, 0 ),
        IF ( __month < 10, "P0" & __month, "P" & __month ))

[vjnvinod]

@Anonymous 

its throwing up some other errror, see below

here is a sample pbix

https://www.dropbox.com/s/boyrdc6kb7xdbdj/Real%20Estate%20FY20%20P02.pbix?dl=0

if you can help, it will be really great

[Anonymous]

My measure seems to work. But you only have data for P02?

The variable will return Q3 for the current date and everything is therefore 0, so it is tricky to validate.

TER = 
VAR __month =
    MONTH ( TODAY () )
VAR __variable =
    IF (
        __month IN { 3; 6; 9 };
        "Q"
            & ROUNDUP ( __month / 3; 0 );
        IF ( __month < 10; "P0" & __month; "P" & __month )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Account Listing'[Quarter] ) = "Q2";
        CALCULATE ( [Gterrevenue]; 'Account Listing'[Quarter] = "Q2" )
            - CALCULATE ( [Gterrevenue]; 'Account Listing'[Quarter] = "Q1" );
        IF (
            SELECTEDVALUE ( 'Account Listing'[Quarter] ) = "Q1";
            CALCULATE ( [Gterrevenue]; 'Account Listing'[Quarter] = "Q1" );
            IF (
                SELECTEDVALUE ( 'Account Listing'[Quarter] ) = "Q3";
                CALCULATE ( [Gterrevenue]; 'Account Listing'[Quarter] = "Q3" )
                    - CALCULATE ( [Gterrevenue]; 'Account Listing'[Quarter] = "Q2" );
                CALCULATE ( [Gterrevenue]; 'Account Listing'[Quarter] = __variable )
            )
        )
    )

[vjnvinod]

@Anonymous 

that is how the data is

i have P02 now, next month i will have P03, which become Q1 and after that P05

& its the reason you see the quarter filter there, so that pepole can filter out the information based on selection they make.

so i assume the below measure will not help, if it returns "0"?

[Anonymous]

I cannot see where you can select a month in your workbook. But I guess that you can just change the __month to be for the month you have data for.

If that is not the case then I do not get your problem.