Create a Table with Column including MAX Date and group by country

[nssidhu74]

How do a translate the following Query. I want to create new table like the one in the following Query.

(NOTE: Updated the Query from the Incorrectone)

Select Distinct(SourceCountry), 

  ( Select MAX(Data_date) from FactInventory FI Where FI.SourceCountry = I.SourceCountry) AS MaxDate,

 ( Select Sum(Volume) from FactInventory FI Where FI.SourceCountry = I.SourceCountry and FI.Data_Date = ( Select MAX(Data_date) from FactInventory FII Where FII.SourceCountry = I.SourceCountry) ) AS SumVolume

       From FactInventory I

[mwegener · Accepted solution]

Hi @nssidhu74 ,

look at this.

https://docs.microsoft.com/en-us/dax/summarize-function-dax

If I answer you question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[mwegener]

Hi @nssidhu74 ,

has your question been answered?

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[mwegener]

Hi @nssidhu74 ,

load the FactSales table into Power BI.

Choose the table visual, put the columns into the Values field.

Set the aggregation for the column Data_Date to Lastest and sort the table visual by the Data_Date column.

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[nssidhu74]

I want to Create New Table, I want to get the Selected Value like SourceCountry and Date Field using SelectedValues formula.

So essentially looking for something like 
NEWTable = DAX Expression(.....................)

[v-kelly-msft · Accepted solution]

Hi @nssidhu74 ,

As what you have requested, you need to create a new table as below:

NEW table = SUMMARIZE('FactSales','FactSales'[SourceCountry],'FactSales'[Data_Date],"Data_Date DESC",MAX('FactSales'[Data_Date]),"VOLUME",SUM('FactSales'[VOLUME]))

Then in the new table, turn to the column  you wanna sort by  and click on the drop-down button, choosing “sort descending” ,finally you will see as below:

For the related .pbix file, you can turn to the URL: https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-kellya_microsoft_com/EWwtQnkFjdhIvflUHyW-zk8...

Hope this would help.

Best Regards,

Kelly

[mwegener · Accepted solution]

Hi @nssidhu74 ,

look at this.

https://docs.microsoft.com/en-us/dax/summarize-function-dax

If I answer you question, please mark my post as solution, this will also help others.

Please give Kudos for support.