Forum Discussion

PowerBi2bPro's avatar
PowerBi2bPro
Icon for Helper I rankHelper I
2 years ago

Date Function or DAX that returns value from a specific column

Hi Everyone,

 

I am looking to get some direction. I am new to this forum and Power BI. I am sharing an example of the table to explain my question.

 

I have a dataset that has quarterly data for a few financial measures. Date and Total expense are part of it. 

 

Year / Date colun contains date the way I have it displayed below. Correspondingly, we have value for Total Expense in the other column. The total expenses is a cumulative data for the year so Dec 2023 value contains total expenses of the year to date. 

 

I want to calculate that difference between December and September 2023 to know what was the expense for the last quarter.  


Can you please get me started?  Thanks a lot in advance. 

 

 

 

Year (Date)Total Expense
December 31, 202310000
September 30, 20238000

4 Replies

  • PowerBi2bPro's avatar
    PowerBi2bPro
    Icon for Helper I rankHelper I
    2 years ago

    So my goal was to calculate the difference. I used Prv_Qtr_Assets = CALCULATE(sum ('Income Statement $'[Average Assets]), DATEADD('Income$MasterDate'[Date].[Date],-1,QUARTER)) this command to get side by side values for Assets but its not working as the column remains blank. 

     

    If there was  value my hope was that I can just deduct one column from the other. Am I on right track?

  • talespin's avatar
    talespin
    Icon for Solution Sage rankSolution Sage
    2 years ago

    hi PowerBi2bPro ,

     

    Is this what you need?

    Logic Used

    Find Expense at beginning of previous Quarter minus one day. That will give YTD till end of previous to previous quarter.

    Then find expense at end of previous quarter

    Subtract two.

     

    Also I am using a Date table linked to Fact table one to many relationship on Date column.

     

    Measure

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

    Expense Previous Qtr =
    VAR _PreviousQtrMinus1Day = STARTOFQUARTER(PREVIOUSQUARTER(FILTER( ALL('CALENDAR'[Date]), 'CALENDAR'[Date] = TODAY()))) - 1
    VAR _EndPreviousQtr = ENDOFQUARTER(PREVIOUSQUARTER( FILTER( ALL('CALENDAR'[Date]), 'CALENDAR'[Date] = TODAY()) ))
    VAR _ValEndPreviousQtr = CALCULATE( VALUES(TestTbl6[YTD Expense]), REMOVEFILTERS(TestTbl6), 'CALENDAR'[Date] = _EndPreviousQtr)
    VAR _ValBeginningPreviousQtr = CALCULATE( VALUES(TestTbl6[YTD Expense]), REMOVEFILTERS(TestTbl6), 'CALENDAR'[Date] = _PreviousQtrMinus1Day )

    RETURN IF( ISBLANK(MAX(TestTbl6[Dt])) , BLANK(), IF( ISBLANK(_ValBeginningPreviousQtr), "N/A", _ValEndPreviousQtr - _ValBeginningPreviousQtr) )
     

     

    • PowerBi2bPro's avatar
      PowerBi2bPro
      Icon for Helper I rankHelper I
      2 years ago

      Thank you so much for sharing your thoughts. I have specific dates such as Dec 31 2023 that I am using as Quarter start/end date.  I am having hard time explaining this. I will try again...

       

      So date Columns has following entries

       

      31 Dec 2023

      30 Sep 2023

       

      Expense Column has the following cooresponsing entries

       

      15,000

      10,000

       

      Now lets say we wanted to know the difference between Sep and December expense. In this case it would be 5,000 

       

      How can I do this in Power BI?

       

      • talespin's avatar
        talespin
        Icon for Solution Sage rankSolution Sage
        2 years ago

        hi PowerBi2bPro ,

         

        Create a Date table, this table should have all the dates(all dates from Min of your date column to maximum of date column), along with any other necessary columns like month, year.

        Join this table to your table on Date column(Make sure data type is date).

        Something like this

        VAR _MinDate = MIN(Date column)
        VAR _MaxDate = MAX(Date column)
        CALENDAR(_MinDate, _MaxDate)

         

        Then use the Measure.