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

      Hi,

      You may download my PBI file from here.

      Hope this helps.