Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Show 0 when there is no sales

    Silver Contributor
    Posted Jan 05, 2022 04:33 AM
    I have a line chart which shows sales amount by month and year wise. When I select 2021 in the slicer it would show for the last 12 month sales based on the measure created, ie.,
    Sales amount 12 month =
    ---------------------------------------------------------
    VAR _selectedAltYear =
    SELECTEDVALUE( 'To Year'[Year Number])
    -----------------------------------------------------
    //new date table
    VAR __rlvntDateTable =
    CALCULATETABLE(
    'Date',
    FILTER('Date','Date'[Date]>= edate([Max Date of To Year],-12) && 'Date'[Date]<= [Max Date of To Year])
    )
    ---------------------------------------------------------
    VAR _altsalesamount =
    CALCULATE( [Sales Amount], __rlvntDateTable )
    RETURN
    _altsalesamount

    Whichever year is selected in the slicer the last 12 month sales is shown for that, but if 2021 is selected and it has sales from january to november only then ideally the measure should show the result from december 2020 to november 2021 which is not happening.
    So I have 2 requirements
    1. Either it should show last 12 months sales from the year what is selected in the slicer.
    2. Else if it can show only the months in the given year then it should show 0 for the months where there is no sales.
    So in the above image december cannot be seen, is it possible to show a 0 for december in the graph.

    Also another issue is that if we select 2022, it shows from jan 2022 to jan 2021. But I want to see only the last 12 completed months. I created a measure -                  Month_Measure = IF(MAX('Date'[Date])<DATE(YEAR(TODAY()),MONTH(TODAY()),1),1,0)                                                                                                                                       and added this as a filter to the visual. Is it possilble to add this in the above given measure



    ------------------------------
    Anu Radha
    98402454127
    ------------------------------


  • 2.  RE: Show 0 when there is no sales

    Silver Contributor
    Posted Jan 05, 2022 04:44 AM
    Hi,

    To replace nulls by zero, the easiest way I know is to add +0 by the end of your measure calculating Sales.
    Sales = SUM([Amount sales])
    will become
    Sales = SUM([Amount sales])+0

    That should do the trick. Have a nice day.

    ------------------------------
    Olivier
    ------------------------------