Forum Discussion

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

Showing blank row and sum on Matrix when using Calculate Measure

Hello Power BI Community

 

I have a dataset which contains date and daily sales revenue for 2 years.

I want to show the total sales volume aggreagated by each month only for fiscal year using matrix.

 

Here is the Dax I wrote.

 

CALCULATE (
    SUM('SalesData'[sales]),
    VAR FirstFiscalMonth = 3 -- Set the first month of the fiscal year
 
    VAR LastDay =
        MAX ( 'Calendar'[Date] )
    VAR LastMonth =
        MONTH ( LastDay )
    VAR LastYear =
        YEAR ( LastDay )
            - IF ( LastMonth < FirstFiscalMonth, 1 )
    VAR FilterYtd =
        DATESBETWEEN (
            'Calendar'[Date],
            DATE ( LastYear, FirstFiscalMonth, 1 ),
            LastDay
        )
    RETURN
        FilterYtd
)
 
The outout seems to be doing what I want to do but showing blank rows in matrix.

 

I can of course use filter pain to remove the blank but why is the matrix showing blank row when there are no value to be shown?
The calculate measure is filtering for particular date so I thought it should only show for September to February.
Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    PeraZo , if there null/blank dates in join they will come even if the filter is removing other non blank values.

     

    In such measure, we add condition

     

     

    CALCULATE (
    SUM('SalesData'[sales]),
    VAR FirstFiscalMonth = 3 -- Set the first month of the fiscal year

    VAR LastDay =
    MAX ( 'Calendar'[Date] )
    VAR LastMonth =
    MONTH ( LastDay )
    VAR LastYear =
    YEAR ( LastDay )
    - IF ( LastMonth < FirstFiscalMonth, 1 )
    VAR FilterYtd =
    DATESBETWEEN (
    'Calendar'[Date],
    DATE ( LastYear, FirstFiscalMonth, 1 ),
    LastDay
    )
    RETURN
    FilterYtd
    , not(isblank('SalesData'[sales date]))
    )