Calculate Average on bases of 31st December but Empty Result for other rows

[Rana_Rumeel]

Hi Everyone,

I have following measure to calculate Sales for same day of each month

00 - Sales_SameDay_EachMonth =
VAR PreviousYearFilter =
    YEAR ( TODAY () ) - 1
RETURN
    CALCULATE (
        SUM ( vw_Sale[Sales_Dollars] ),
        YEAR ( vw_SalesCollectionsCenter[Date - Sales] ) = PreviousYearFilter
    )

Here is the result of this measure

 
And here is the 2nd measure to calculate Sale for 31st date of each month for last year.

00 - Total Sales 31st of Last Year =
VAR PreviousYearFilter =
    YEAR ( TODAY () ) - 1
RETURN
    CALCULATE (
        SUM ( vw_Sale[Sales_Dollars] ),
        DAY ( vw_SalesCollectionsCenter[Date - Sales] ) = 31,
        YEAR ( vw_SalesCollectionsCenter[Date - Sales] ) = PreviousYearFilter
    )

 and here is the result of this measure

Now I created a 3rd measure to calculate the Average on the bases of these two measure

00 - Total Average =
DIVIDE (
    [00 - Sales_SameDay_EachMonth],
    [00 - Total Sales 31st of Last Year],
    0
)

and here is the result

I want to see the Average for each Date. but it is displaying only for 31st. Kindly suggest

[v-yohua-msft]

Hi, @Rana_Rumeel 

Based on your information, I created a table:

There's nothing wrong with your first and third measures. I modified your second measure, and my understanding is that you want to divide the data for each day of the month by the data for the last day of the month, so I created the following measure:

00 - Total Sales 31st of Last Year = 
CALCULATE (
    SUM ( 'Table'[Sales_Dollars]),
    FILTER (
        ALL ( 'Table' ),
       'Table'[Date - Sales] = EOMONTH ( MAX ( 'Table'[Date - Sales]), 0 )
    )
)

Here is my preview:

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rana_Rumeel]

@v-yohua-msft Thank you for yur response.
I have two tables in my model
    1: vw_SalesCollectionsCenter[Date - Sales]
    2: vw_Sale[Sales_Dollars]

1st table contains Dimension information and contains a Date column
I am using only Day part to display sales only at Day level for each month for example
-------------------------------------------------------------------
1 = Sum Sale for 1st date of every month
2 = Sum Sale for 2nd date of each month
..
..
31 = Sum Sale for 31st of each month.
------------------------------------------------------------------

and 2nd table contains data for sales. there is a relationship on the bases of DateKey.

I need to divide Sales on 1st (Sum Sale for 1st date of every month from last year only)
by Sales on 31st (Sum Sale for 31st date of every month from last year)

kindly confirm if more clarity is required.

[some_bih]

Hi @Rana_Rumeel without model / and details it is hard to spot your issue.

Still, try 2 test measures (order of creation is important, so 00 - Total Average test is created last)

00 - Total Sales Last Year test =
VAR PreviousYearFilter =
YEAR ( TODAY () ) - 1
RETURN
CALCULATE (
SUM ( vw_Sale[Sales_Dollars] ),
YEAR ( vw_SalesCollectionsCenter[Date - Sales] ) = PreviousYearFilter
)

00 - Total Average test=
DIVIDE (
[00 - Sales_SameDay_EachMonth],
[00 - Total Sales Last Year test],
0
)