latest submitted data over time

[Anonymous]

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	188	12/5/2018	1/15/2019
6	564	12/19/2018	1/18/2019
2	384	1/15/2019	2/25/2019

The calculation I want is Rate = ([Products] / [Sales]) * 1000

But I want it only for the latest submitted data. Currently I have a date slicer set up on [Reporting Month]. An example of what I want to happen is if the date slicer is set to December 2018 then I want the calculated rate to be from the data submitted on 1/18/2019 or (6/564)*1000 and if I have the date slicer as Nov through Dec 2018 then I want the calculation to use data submitted 1/18/2019 and 12/05/2018 which would be ((4+6)/(985+564))*1000

I tried a calculation with LASTDATE but it seems like it is just finding the lastdate in the whole column and does not change with the slicer. 

This is the measure I am using from my last post (big thanks to @VasTg for help) and I think I need a new measure for displaying multiple months together:

Measure = 
VAR MAX_SUBMITTED_DATE = MAX('Table'[Submitted Date])
RETURN CALCULATE((SUM('Table'[Products])/SUM('Table'[Sales]))*1000,FILTER('Table','Table'[Submitted Date]=MAX_SUBMITTED_DATE))

for the purpose of displaying one month at a time then this works perfectly. I did not realize at the time that I would need it to display differently and show the rate from previous months together. If i want to display the rate on a card for one month it works and does what it should and if I display a line graph or some broken up by month then it works great! but if I want to display the card and calulcate for the previous 3 months then I get the most recent submission (which is what I asked for so I am sorry to post again). 

thanks for any help

[v-lid-msft]

Hi @Anonymous ,

We can try to use the following measure to meet your requirement:

Measure =
VAR t =
    SUMMARIZE (
        'Table',
        'Table'[Submitted Date].[Year],
        'Table'[Submitted 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]

@v-lid-msft For some reason this is not working for me. What is the date slicer set to in your example? In my dashboard I have a date table that is related to this example table on reporting date. Could that be my issue?

EDIT: in my real date I created a table as in my example and your solution does work. It just does not work on my actual data. Could it be because in my actual data the Submitted Date column is Date/Time type instead of just Date?

EDIT 2: I changed the data type to date only and still no luck. When I try this measure on my data it adds all the sales and all the products together no matter the submission date criteria. 

[Anonymous]

@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 · Accepted solution]

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]

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

[amitchandak]

I did not get completed. When selected Nov and Dec, you are choosing dec and Jan date. Second, if you are only looking at GT and you need dates from month data needs to be grouped at month, if you need two dates, one from each month. values or summarize need to be used

Try like

Measure = 
var _min_sel = minx(allselected('Table'[Submitted Date]),'Table'[Submitted Date])
VAR MAX_SUBMITTED_DATE = MAX('Table'[Submitted Date])
RETURN CALCULATE((SUM('Table'[Products])/SUM('Table'[Sales]))*1000,values(Date[Month-Year]),FILTER('Table','Table'[Submitted Date]=MAX_SUBMITTED_DATE && 'Table'[Submitted Date]>_min_sel))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin