Retrieving a value

[Anonymous]

I am pretty bigginer in DAX and trying to write a formula to find Net as at 31/12/2017 in below table which is -313978.32. Could you please help me?

[Greg_Deckler]

Don't see a table, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Hi Greg,

thank you for your prompt reply. And also thank you for the link. It was really usefull. Here is my table. I did not know that I could copy and paste it. I am trying using a dDate table to pick up GST net value as at 31/12/2017 but I recieve a blank result. 

I really appreciate your help. 

oops looks like I have forgotten to include coloumn headers. they are as below

Date

GSTDr

GSTCr

GSTNet

DateAmt_DrAmt_CrNet

1/01/2017 12:00:00 AM	-210976.93	300624.16	89647.23
1/02/2017 12:00:00 AM	-105108.5	1210.19	-103898.31
1/03/2017 12:00:00 AM	-135563.1	19567.73	-115995.37
1/04/2017 12:00:00 AM	-65673.88	15132.5	-50541.38
1/05/2017 12:00:00 AM	-76804.84	14196.43	-62608.41
1/06/2017 12:00:00 AM	-68038.29	39244.6	-28793.69
1/07/2017 12:00:00 AM	-126062	148130	22068
1/08/2017 12:00:00 AM	-52081	10428	-41653
1/09/2017 12:00:00 AM	-120374	20207	-100167
1/10/2017 12:00:00 AM	-180843	168515	-12328
1/11/2017 12:00:00 AM	-117300	11996	-105304
31/12/2017 12:00:00 AM	-318318.44	4340.12	-313978.32
1/01/2018 12:00:00 AM	-67212	310336	243124
1/02/2018 12:00:00 AM	-132821.28	9762.55	-123058.73
1/03/2018 12:00:00 AM	-96356.11	17676.18	-78679.93

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-yuta-msft]

@Anonymous ,

Create a measure using dax below:

Result = CALCULATE(MAX('Table'[Net]), FILTER('Table', 'Table'[Date] = DATEVALUE("31/12/2017")))

 

Community Support Team _ Jimmy Tao

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

[Anonymous]

Hi ,

Thank you for your reply. The solution works if I want to calculate Total GST for financial  year ended to 31/12/2018 as GST net balance at 31/12/2017 is required to calculate total GST recieved in 2018. However if I want to dasame for financial year ended to 31/12/2017 i will need to have GST net amount as at 31/12/2016. In other words DATEVALUE must be dynamic. Could you please help me to acieve this.

regards

[Ashish_Mathur]

Hi,

I am not clear with your question.  Please show the exact result you are expecting.

[Anonymous]

Hi,

Sorry If I could not explain it well. I am trying to build a cash flow report. The report will have Year and Months slices and the users can select the year and the month for which they want to prepare cash flow report. One of the items which will be shown on the report is Net GST. it is calculated as the difference between Net GST amount at the end of selected month and at the end of last financial year.

For example if the user selects Year 2018 and month February, the Net GST shown on the report will be calculated as the difference between Net GST at 28/2/2018 and Net GST at 31/12/2017. Hope this explains the issue.

Regards

Gus

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Hi Ashish,

It works perfectly. Thank you ver much.

Much appreciated.

Gus

[Ashish_Mathur]

You are welcome.

[Anonymous]

Hi Tao,

Thank you for your reply. The solution works if I want to calculate Total GST for financial  year ended to 31/12/2018 as GST net balance as at 31/12/2017 is required to calculate total GST recieved in 2018. However if I want to d o same for financial year ended to 31/12/2017 i will need to have GST net amount as at 31/12/2016. In other words DATEVALUE section of the formula should be dynamic. Could you please help me to achieve this.

regards