Time Intelligence: Previous Month Value is Incorrect

[VikrantC]

Hi,

I have 3 month daily data for 4 players (X1, X2, X3 and Y1).  The data is arranged in 3 columns (Date, Player, Score).  All the players have data for each date (no gaps).   There is no score on weekend (no rows for weekend).  I am able to get Score for end of the month (specific date).  However, I am unable to get Data for the previous month (end of the month).  I would like to see scores for X1, X2, X3 and Y1 on 2/28, 1/31, 12/31 in one column and calculate % change from the previous month.

The second problem I am having is I want to Filter X1 and X2 (on date) and use variable to calculate Sum of X1, or (X1*Y1^2).  Please see the sample data in the file below.

PBI_Data (file://DESKTOP-47L4URJ/PBI_Data)PBI_Data (file://DESKTOP-47L4URJ/PBI_Data) 

I am not sure how to attach PBI file.  Pease let me know if the folder cannot be open (Dropbox).

[v-kkf-msft · Accepted solution]

Hi @VikrantC ,

I do not have access to the link you shared, please use SharePoint Online or other tools to share your file.

Based on my understanding, I created the following example data.

1. Then create the measure to calculate the month end score value and compare it to the previous month end.

MonthEndScore = 
CALCULATE (
    SUM ( 'Table'[Score] ),
    FILTER ( 'Table', 'Table'[Date] = EOMONTH ( 'Table'[Date], 0 ) )
)

Score% = 
IF (
    [MonthEndScore] <> BLANK (),
    DIVIDE (
        [MonthEndScore],
        CALCULATE (
            [MonthEndScore],
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Date] = EOMONTH ( MAX ( 'Table'[Date] ), -1 )
                    && 'Table'[Player] = MAX ( 'Table'[Player] )
            )
        ),
        1
    ) - 1
)

2. Create the measure to calculate X1*Y1^2.

X1*Y1^2 = 
CALCULATE ( SUM ( 'Table'[Score] ), 'Table'[Player] = "X1" )
    * CALCULATE ( POWER ( SUM ( 'Table'[Score] ), 2 ), 'Table'[Player] = "Y1" )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-kkf-msft · Accepted solution]

Hi @VikrantC ,

I do not have access to the link you shared, please use SharePoint Online or other tools to share your file.

Based on my understanding, I created the following example data.

1. Then create the measure to calculate the month end score value and compare it to the previous month end.

MonthEndScore = 
CALCULATE (
    SUM ( 'Table'[Score] ),
    FILTER ( 'Table', 'Table'[Date] = EOMONTH ( 'Table'[Date], 0 ) )
)

Score% = 
IF (
    [MonthEndScore] <> BLANK (),
    DIVIDE (
        [MonthEndScore],
        CALCULATE (
            [MonthEndScore],
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Date] = EOMONTH ( MAX ( 'Table'[Date] ), -1 )
                    && 'Table'[Player] = MAX ( 'Table'[Player] )
            )
        ),
        1
    ) - 1
)

2. Create the measure to calculate X1*Y1^2.

X1*Y1^2 = 
CALCULATE ( SUM ( 'Table'[Score] ), 'Table'[Player] = "X1" )
    * CALCULATE ( POWER ( SUM ( 'Table'[Score] ), 2 ), 'Table'[Player] = "Y1" )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VikrantC]

One more question to add to the earlier question.  How about I used % of change per month (instead of each date).  Would it be easier to summarize X1, Y1 in columns?

[VikrantC]

Thank you very much.  This is working.    I have one question.  Regarding X1*Y12.  How do I use the Measure Score%.  

Eventually, I was to use a Variable for (X1*Y1^2) using the Score% (% change over month for X1 and Y1).

Again, thank you very much.

Best Regards,

VC

[v-kkf-msft · Accepted solution]

Hi @VikrantC ,

Sorry for the late reply. Please try the following formula:

Measure = 
CALCULATE ( [Score%], 'Table'[Player] = "X1" )
    * CALCULATE ( POWER ( [Score%], 2 ), 'Table'[Player] = "Y1" )

Best Regards,
Winniz

[VikrantC]

Looks good.  I am going to try it out tonight.  Have a great day.

[VikrantC]

Hi Amit,

Your formula did not work.  I am more interested in the second problem.  Any idea?.  Thanks.

[VikrantC]

Hi Amit,

Thank you very much.  I used a similar formula.  However, I am still stuck with "The second problem I am having is I want to Filter X1 and X2 (on date) and use variable to calculate Sum of X1, or (X1*Y1^2).  Please see the sample data in the file below".    The X1 and X2 have same dates and values.

If I have two variables (X1 and X2) with dates and values (3 columns total).  How do I manipulate the formula.  For example, I want to SUM (X1^2*X2).  Basically, for each specific dates I want to calculate X1square and multiply it with X2,  Ideally, I want this to be a variable so that I can use it as part of other DAX formula.    

[amitchandak]

@VikrantC , Based on what I got last date of the month

example 

MTD Sales = CALCULATE(lastnonblankvalues('Date'[Date],SUM(Sales[Sales Amount])),DATESMTD('Date'[Date]))

[VikrantC]

Thanks Amit.  For some reason it did not work.  I did get it.  Again, thank you very much for your help.