Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Retrieving a value

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?            
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.93300624.1689647.23
1/02/2017 12:00:00 AM-105108.51210.19-103898.31
1/03/2017 12:00:00 AM-135563.119567.73-115995.37
1/04/2017 12:00:00 AM-65673.8815132.5-50541.38
1/05/2017 12:00:00 AM-76804.8414196.43-62608.41
1/06/2017 12:00:00 AM-68038.2939244.6-28793.69
1/07/2017 12:00:00 AM-12606214813022068
1/08/2017 12:00:00 AM-5208110428-41653
1/09/2017 12:00:00 AM-12037420207-100167
1/10/2017 12:00:00 AM-180843168515-12328
1/11/2017 12:00:00 AM-11730011996-105304
31/12/2017 12:00:00 AM-318318.444340.12-313978.32
1/01/2018 12:00:00 AM-67212310336243124
1/02/2018 12:00:00 AM-132821.289762.55-123058.73
1/03/2018 12:00:00 AM-96356.1117676.18-78679.93
v-yuta-msft's avatar
v-yuta-msft
Community Support
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago
    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
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    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's avatar
      Ashish_Mathur
      Super User
      6 years ago

      Hi,

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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