Calculated column without using the CALCULATE function

[crln-blue]

Hi, I'm still a newbie in PowerBI and I'm trying to get a data with the use of filters but no data was generated from the formula.

For context, I have a (summarized) table with Month, Year, Type (category column) and Total (measure). I want to add another column where the data is the total measure of the month-past year data.

For example:

My current formula is: 

MonthPastYear = CALCULATE(SUM('Table'[Current Total]),'Table'[Month]='Table'[Month],'Table'[Year] = 'Table'[Year] - 1, 'Table'[TYPE] = 'Table'[TYPE])

No data generated from the column. I don't think that CALCULATE SUM is the correct function to be used since the table is a summarized table that I made. FILTERS function isn't right. Any help is appreciated. Thank you!

[crln-blue · Accepted solution]

Hello, gonna close this thread now since I found a case similar to mine:

https://community.powerbi.com/t5/Desktop/How-to-calculate-sales-same-period-last-year-using-two-date...

I applied the solution and it worked. Thanks everyone!

[Fowmy]

@crln-blue 

Can ou try this measure:

MonthPastYear = 
CALCULATE(
    SUM('Table'[Current Total]),
    FILTER( ALL('Table'),
        'Table'[Month]=MAX('Table'[Month]) && 
        'Table'[Year] = MAX('Table'[Year]) - 1 && 
        'Table'[TYPE] = MAX('Table'[TYPE])
    )
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[crln-blue]

Hi @Fowmy , thanks for the reply. I tried your suggestion but the data generated isn't right. It gives off only one measure across all rows, maybe because of the MAX function.

[Fowmy]

@crln-blue 

Can you share some sample data to check ?

you paste your data here

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Greg_Deckler]

@crln-blue You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

Also, since you're a New Member, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[crln-blue]

HI @Greg_Deckler , thanks for the links. I checked them all and my case seems to be matched with getting data using the SAMEPERIODLASTYEAR. I tried using it the way the docs did but unsuccessful. I also checked the counterpart in your link which is TO **bleep** WITH DATEADD. However, I find it a bit confusing (sorry, I always have a hard time without examples) and I don't know how to use it in my formula since I only have months and years. 

Your links are helpful and gonna use it on the future.