Forum Discussion

admin11's avatar
admin11
Memorable Member
5 years ago
Solved

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

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

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

I get Blank output.

 

PBI file :-

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

 

Paul

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

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

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
        amitchandak
        Super User
        5 years ago

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