cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
admin11
Post Prodigy
Post Prodigy

How to Create Current Month P&L Table ?

Hi All

i am able create _YTD_REV table , using below expression :-

_YTD REV = CALCULATE(CALCULATE(GL[AMOUNT],DATESYTD('Date'[Date],"12/31")), GL[1_REV] = "REV")

admin11_0-1619706606559.png

I also able to create Current Month REV Table , using below expression :-

Current month - 3 REV =
VAR _Start = EOMONTH ( TODAY (), -4 ) + 1
VAR _End = EOMONTH ( _Start, 0 )
RETURN
CALCULATE ( [AMOUNT_REV], DATESBETWEEN ( 'Date'[Date], _Start, _End ) )

admin11_1-1619706700929.png

I try to create P&L table for Current Month 1 expression :-

 
Current month - 1 =
VAR _Start = EOMONTH ( TODAY (), -2 ) + 1
VAR _End = EOMONTH ( _Start, 0 )
RETURN
CALCULATE ( 'GL'[AMOUNT], DATESBETWEEN ( 'Date'[Date], _Start, _End ) )
 

admin11_2-1619706841093.png

I get Blank output.

 

PBI file :-

https://www.dropbox.com/s/2otguqfyq9fxy3p/PBT_V2021_400%20GL_TI.pbix?dl=0

 

Paul

1 ACCEPTED SOLUTION

@admin11 , My mistake forget , after all(date) 

 

Current month - 1 =
VAR _Start = EOMONTH ( TODAY (), -2 ) + 1
VAR _End = EOMONTH ( _Start, -1 )
RETURN
CALCULATE ( 'GL'[AMOUNT], Filter (all('Date') , 'Date'[Date]<= _Start && 'Date'[Date] >= _End ) )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
amitchandak
Super User IV
Super User IV

@admin11 , Try like

 

Current month - 1 =
VAR _Start = EOMONTH ( TODAY (), -2 ) + 1
VAR _End = EOMONTH ( _Start, -1 )
RETURN
CALCULATE ( 'GL'[AMOUNT], Filter (all('Date') 'Date'[Date]<= _Start && 'Date'[Date] >= _End ) )

 

 

and

 

Current month  =
VAR _Start = EOMONTH ( TODAY (), -1 ) + 1
VAR _End = EOMONTH ( _Start, 0 )
RETURN
CALCULATE ( 'GL'[AMOUNT], Filter (all('Date') 'Date'[Date]<= _Start && 'Date'[Date] >= _End ) )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak 

Thank you for sharing , both your expression , i get error below :-

admin11_0-1619736316767.pngadmin11_1-1619737034467.png

 

@admin11 , My mistake forget , after all(date) 

 

Current month - 1 =
VAR _Start = EOMONTH ( TODAY (), -2 ) + 1
VAR _End = EOMONTH ( _Start, -1 )
RETURN
CALCULATE ( 'GL'[AMOUNT], Filter (all('Date') , 'Date'[Date]<= _Start && 'Date'[Date] >= _End ) )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

@amitchandak 

Your expression working fine. Thank you

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors