CODEWITHSUNDEEP
powerbidaxformula
Ryiah
Ryiah

Reputation: 1

YTD Value not showing in the Matrix Table if the row does not have value in the data for the selected month

I have the following in my view:

Matrix Table which shows the YTD Sales by Department. Slicer: Fiscal Month

Here's my YTD formula:

        VAR SelectedMonth = MAX(DIM_CALENDAR_SALES[FSMONTH])
        RETURN
            CALCULATE(
                TOTALYTD(
                    SUM(STG_SD[CSAMTNET]), 
                    DIM_CALENDAR_SALES[FSDATE]
                ),
            FILTER(
                ALL(DIM_CALENDAR_SALES),
                MONTH(DIM_CALENDAR_SALES[FSDATE]) <= SelectedMonth
    )
)

YTD Sales: Should be computed for the current year up until the selected month in the slicer

Department A latest sales was in Feb this Year, so technically the department should have a YTD Sales value in the Matrix. But, when I filter it the Current month, Department A sales is included in the total, but does not show in the Matrix Table.

For comparison I added a field, YTD Show to show the supposed value for each row. It is just the total YTD sales removing the date slicer

I'm new in Power BI and have redesigned my YTD formula multiple times but still show the same result.

TOTALYTD and DATESYTD doesn't work as well with the slicer filter as it only filters the value to the selected month.

EDIT:

For Reference, this is being sliced as the FSMONTH = 3. And as you can see the value is not reflecting on the matrix table as it is from FSMONTH = 2. enter image description here

Upvotes: 0

Views: 243

Answers (1)

Sam Nseir
Sam Nseir

Reputation: 12131

If you have a relationship between STG_SD and DIM_CALENDAR_SALES then you shouldn't need to handle the slicer any differently. Try:

RETURN
  TOTALYTD(
    SUM(STG_SD[CSAMTNET]), 
    DIM_CALENDAR_SALES[FSDATE]
  )

If you don't have a relationship, then try:

RETURN
  TOTALYTD(
    SUM(STG_SD[CSAMTNET]), 
    DIM_CALENDAR_SALES[FSDATE],
    MONTH(DIM_CALENDAR_SALES[FSDATE]) <= SelectedMonth
  )

Or:

RETURN
  TOTALYTD(
    SUM(STG_SD[CSAMTNET]), 
    DIM_CALENDAR_SALES[FSDATE],
    FILTER(
      ALL(DIM_CALENDAR_SALES),
      MONTH(DIM_CALENDAR_SALES[FSDATE]) <= SelectedMonth
    )
  )

Upvotes: 0

Related Questions