cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
themistoklis Memorable Member
Memorable Member

MTD Previous Year on Pivot Table

Hello All,

 

Im trying to create a DAX formula for MTD for previous year.

I want to insert this formula to a Pivot Table in PowerBI.

 

I have used multiple formulas found from various websites but with no lack.

 

The Pivot table has one dimension for rows and the Year-Month (from Custom Calendar) as columns.

For current month im trying to create a formula for MTD previous year.

 

All formulas that i have created bring the total for same month from previous year.

For all completed months (e.g. Jan to Mar the data are correct).

 

Im wondering whether the use of PivotTable on PowerBI has to do with this issue.

 

At the moment im working on this formula:

CALCULATE(SUM('Invoices to Clients'[TurnOver]),
        DATESBETWEEN('Calendar'[Date],                    
            FIRSTDATE(DATEADD('Calendar'[Date],-12,MONTH)),   
            LASTDATE(DATEADD('Calendar'[Date],-12,MONTH))  
         )
     )

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
themistoklis Memorable Member
Memorable Member

Re: MTD Previous Year on Pivot Table

@Greg_Deckler

Next time i will try to send a sample dataset.

 

@Ashish_Mathur

I tried this but with no luck. I am a bit surprised why this formula didnt work.

 

@v-yulgu-msft

I was the last person who replied to this thread and i provided the following formula. I used the EDATE function.

Turnover MTD (PY) = CALCULATE(SUM('Invoices to Clients'[TurnOver]),
        DATESBETWEEN('Calendar'[Date],                    
            FIRSTDATE(DATEADD('Calendar'[Date],-12,MONTH)),   
            IF(MONTH(MIN('Calendar'[Date])) = MONTH(TODAY()), EDATE(TODAY(),-12) , LASTDATE(DATEADD('Calendar'[Date],-12,MONTH))) 
         )
     )

View solution in original post

4 REPLIES 4
Super User IV
Super User IV

Re: MTD Previous Year on Pivot Table

Sample/example data please. 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


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User IV
Super User IV

Re: MTD Previous Year on Pivot Table

Hi,

 

How about this one

 

=CALCULATE(SUM('Invoices to Clients'[TurnOver]),SAMEPERIODLASTYEAR(Calendar[Date]))


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Microsoft
Microsoft

Re: MTD Previous Year on Pivot Table

Hi @themistoklis,

 

Please check whether this thread helpful to your scenario: Compare MTD with previous period

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
themistoklis Memorable Member
Memorable Member

Re: MTD Previous Year on Pivot Table

@Greg_Deckler

Next time i will try to send a sample dataset.

 

@Ashish_Mathur

I tried this but with no luck. I am a bit surprised why this formula didnt work.

 

@v-yulgu-msft

I was the last person who replied to this thread and i provided the following formula. I used the EDATE function.

Turnover MTD (PY) = CALCULATE(SUM('Invoices to Clients'[TurnOver]),
        DATESBETWEEN('Calendar'[Date],                    
            FIRSTDATE(DATEADD('Calendar'[Date],-12,MONTH)),   
            IF(MONTH(MIN('Calendar'[Date])) = MONTH(TODAY()), EDATE(TODAY(),-12) , LASTDATE(DATEADD('Calendar'[Date],-12,MONTH))) 
         )
     )

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors