Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

latest submitted data over time

Here is an example of the table I have    Products Sales Reporting Date Submitted Date 5 147 10/19/2018 11/16/2018 4 985 11/11/2018 12/5/2018 1 411 12/1/2018 1/3/2019 10 ...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    Sorry for our mistake in the formula, we can try to use the following measure to meet your requirement:

     

    Rate = 
VAR t =
    SUMMARIZE (
        'Table',
        'Table'[Reporting Date].[Year],
        'Table'[Reporting Date].[MonthNo],
        "TotalProduct",
        VAR m =
            MAX ( 'Table'[Submitted Date] )
        RETURN
            CALCULATE ( SUM ( 'Table'[Products] ), 'Table'[Submitted Date] = m ),
        "TotalSales",
        VAR m =
            MAX ( 'Table'[Submitted Date] )
        RETURN
            CALCULATE ( SUM ( 'Table'[Sales] ), 'Table'[Submitted Date] = m )
    )
RETURN
    SUMX ( t, [TotalProduct] ) / SUMX ( t, [TotalSales] ) * 1000

     


    Best regards,

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

@v-lid-msft 

 

Here is a screenshot of what I get when using the measure on my data. I should see 0.56 for oct but instead it shows 0.78 which is the rate if you combine both days in october reporting date. 

v-lid-msft's avatar
v-lid-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

 

Sorry for our mistake in the formula, we can try to use the following measure to meet your requirement:

 

Rate = 
VAR t =
    SUMMARIZE (
        'Table',
        'Table'[Reporting Date].[Year],
        'Table'[Reporting Date].[MonthNo],
        "TotalProduct",
        VAR m =
            MAX ( 'Table'[Submitted Date] )
        RETURN
            CALCULATE ( SUM ( 'Table'[Products] ), 'Table'[Submitted Date] = m ),
        "TotalSales",
        VAR m =
            MAX ( 'Table'[Submitted Date] )
        RETURN
            CALCULATE ( SUM ( 'Table'[Sales] ), 'Table'[Submitted Date] = m )
    )
RETURN
    SUMX ( t, [TotalProduct] ) / SUMX ( t, [TotalSales] ) * 1000

 


Best regards,

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks so much! 

     

    for anyone looking for something similar and using a date table like I am I made one change to the solution from v-lid-msft 

     

    I'm new to powerbi so I hope I explain this clearly enough for someone to follow.

     

    In the Summarize function - Instead of using "Reporting Date" from the Sales table I used the "Date Table" using the column that the relationship is set up on. 

    For exxample I have a many to one relationship from the "Sales Table"[Reporting Date] to "Date Table"[Reporting Date Relationship] so I included that in teh summarize funciton below. 

    Rate = 
VAR t =
    SUMMARIZE (
        'SalesTable',
        'DateTable'[Reporting_Date_Relationship].[Year],
        'DateTable'[Reporting_Date_Relationship].[MonthNo],
        "TotalProduct",
        VAR m =
            MAX ( 'SalesTable'[Submitted Date] )
        RETURN
            CALCULATE ( SUM ( 'SalesTable'[Products] ), 'SalesTable'[Submitted Date] = m ),
        "TotalSales",
        VAR m =
            MAX ( 'SalesTable'[Submitted Date] )
        RETURN
            CALCULATE ( SUM ( 'SalesTable'[Sales] ), 'SalesTable'[Submitted Date] = m )
    )
RETURN
    SUMX ( t, [TotalProduct] ) / SUMX ( t, [TotalSales] ) * 1000

     Thanks again and I hope this helps someone else as well