Variance to last month DAX not working

[msfer]

Hi there 

I have a DAX calculating a particular product's price difference to last month. 

Variance = 

VAR_last = maxx(FILTER('Table1','Table1'[Product]=EARLIER('Table1'[Product])&&'Table1'[Month]=EDATE(EARLIER('Table1'[Month]),-1)),'Table1'[Price])

return if(ISBLANK(_last),BLANK(),('Table1'[Price]-_last))

This has been working fine so far, the problem is, now it seems to be skipping some months.

For example, the report table is coming up blank because the last month value is blank for the first portion of the formula. 

This is the same for February and November. I cannot work out what's gone wrong. 

Hope someone can assist. 

[msfer]

Ok, have figured it out. 

It was such a simple error, nothing to do with the DAX. 

The data source is a spreadsheet, the date has been entered with the last date of the month which varies between 30th, 31st, 28th and 29th. 

Once this was corrected to just month/year (mmm/yyyy) in the spreadsheet, missing months popped up. 

[TomMartens]

Hey @msfer ,

I recommend reading this article, as it provides patterns to solve almost any date/time-related challenges: Time patterns – DAX Patterns

It's never a good idea to use a one-table solution, especially not when date/time-related challenges are involved, you might also want to read this: Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

If the above articles do not to help to tackle your challenge, please take the time to create a pbix file that contains sample data but still reflects your semantic model (tables, relationships, calculated columns, and measures). Upload the pbix file to OneDrive, Google Drive, or dropbox and share the link. If you are using a spreadsheet to create the sample data, share the spreadsheet as well.

Do not forget to explain the expected result based on the sample data you provide.

Regards,

Tom

[msfer]

HI @TomMartens 

Thank you for your reply. 

I don't have a seperate calendar table set up for this calculation. 

The Product, Month and Price are all coming from the same table. 

Are you suggesting I create a calendar table for this calculation? 

[Ashish_Mathur]

Hi,

Share some data, explain the question and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Kishore_KVN]

Hello @msfer ,

By understanding your requirements modified DAX a bit. Please try and let me know if it works or not. 

Variance = 
VAR monthOffset = 1
VAR _last = 
    MAXX(
        FILTER(
            'Table1',
            'Table1'[Product] = EARLIER('Table1'[Product]) &&
            'Table1'[Month] = EDATE(EARLIER('Table1'[Month]), - monthOffset)
        ),
        'Table1'[Price]
    )
RETURN
    IF(
        ISBLANK(_last) && monthOffset < 12, // Limiting to checking 12 months back
        (
            monthOffset = monthOffset + 1;
            MAXX(
                FILTER(
                    'Table1',
                    'Table1'[Product] = EARLIER('Table1'[Product]) &&
                    'Table1'[Month] = EDATE(EARLIER('Table1'[Month]), - monthOffset)
                ),
                'Table1'[Price]
            )
        ),
        IF(ISBLANK(_last), BLANK(), 'Table1'[Price] - _last)
    )

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

[msfer]

Hi @Kishore_KVN 

Thank you for your reply. 

No this DAX seems to be producing the same output. 

Same months seems to be missing variance data