Help creating a DAX measure!

[pk1593]

How to create a 'measure' in Power BI to get previous value using this dataset ?

I have shown the previous value table as an eg. of the desired output. I want the previous data to be referred for eg: the previous data for 2022_Q4 is data in 2022_Q3 and likewise.

I am looking for a code similar to the below (eg: from a code in tableau) which uses lookup function to get the previous value
X = ABS([Measure1]-LOOKUP([Measure1],-1))

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.

[pk1593]

Please use this .pbix file. 

https://drive.google.com/drive/folders/1EV-sQ1ZXhYnZoacPTiL6J4SJsLqW1jun?usp=share_link 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.  The second table shows your desired result.

Hope this helps.

[pk1593]

Thank you Ashish for the help. The previous value is coming correctly but the sum of weights and % of wt calculation there seems an issue. 

As for the sum of weights, the sum of weights need to be distinct by ID - refer Measure !Wtd_Sum (it cannot be sum of total weights as done in 'Wt') and the Measure !Percent is trying to calculate : 

!Percent =

SUM([Weights])/TOTAL(SUM([Weights])) {Tableau code} Total is used so that the denominator doesnt take filter into consideration and it calulates the %. 

The code I have kept for !Percent seems to divide the sum by the total weights in all the timeperiod and is not filtered by respective quarter. The desired result is mentioned in the attached excel file : Link 

[Ashish_Mathur]

I cannot understand what you want.  Someone who does will help you. 

[Arul]

@pk1593 ,

try this measure,

Previous Value = 
VAR _selectedYear =
    IF ( HASONEVALUE ( Test[Year_Quarter] ), 
    FIRSTNONBLANK ( Test[Year_Quarter], 
    Test[Year_Quarter] ), BLANK () ) - 1
RETURN
    IF (
        CALCULATE (
            [Measure],
            ALL ( Test ),
            Test[Year_Quarter] = _selectedYear
        ) = 0,
        0,
        CALCULATE (
            [Measure],
            ALL ( Test ),
            Test[Year_Quarter] = _selectedYear
        )
    )

Thanks,

Arul

[pk1593]

Thanks Arul for the help! But Iam getting an error for the same. 

I have attached the .pbix file - can you please check and fix the same?

https://drive.google.com/drive/folders/1EV-sQ1ZXhYnZoacPTiL6J4SJsLqW1jun?usp=share_link