Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
admin11
Memorable Member
Memorable Member

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 ) )

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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 ) )

@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 ) )

@amitchandak 

Your expression working fine. Thank you

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.