Forum Discussion

fjjpeeters1976's avatar
fjjpeeters1976
Icon for Helper III rankHelper III
1 year ago
Solved

deduct amount from previous selected value in a table

 

hi all,

 

I am downloading regularly a report from our project tracking system. For every project I can see what is the expected spend for the next months. When comparing snapshots I can see if projects are in delay or changing their spend. Below is a report that I have created so far. 

Now I want to create a measure that deducts the value of the last snapshot from the previous on. But I want to make it in a way that the user can select the snapshot themselves. So for example if user wants to see snapshot only from 06/03/2025 with the one for 11/01/2025 that should be possible. So I would need a function that can see the previous line snapshot and use that as a filter in a variable. Could you do this with a window function or the offset function? 

 

 

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    1 year ago

    Thankyou, FBergamaschi, for your response.

    Hi fjjpeeters1976,

    We appreciate your question on the Microsoft Fabric Community Forum.

    Based on my understanding of the issue, please find attached the screenshots and a sample PBIX file that may assist in resolving the matter:

    We hope the information provided helps to resolve the issue. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

    Thank you.

10 Replies

  • FBergamaschi's avatar
    FBergamaschi
    Icon for Super User rankSuper User
    1 year ago

    Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

     

    Need help uploading data? click here

     

    Want faster answers? click here

    • fjjpeeters1976's avatar
      fjjpeeters1976
      Icon for Helper III rankHelper III
      1 year ago
      SnapshotProjectMonthAmount
      04 January 2025Project A01 January 20255
      04 January 2025Project A01 February 202510
      04 January 2025Project A01 March 202515
      04 January 2025Project A01 April 202515
      04 January 2025Project A01 May 202520
      04 January 2025Project A01 June 20255
      04 January 2025Project A01 July 202530
      04 January 2025Project A01 August 20253
      04 January 2025Project A01 September 202544
      04 January 2025Project A01 October 202522
      04 January 2025Project A01 November 20255
      04 January 2025Project A01 December 202512
      04 January 2025Project A01 January 202615
      04 January 2025Project A01 February 202623
      04 January 2025Project A01 March 202611
      04 January 2025Project A01 April 20265
      04 January 2025Project A01 May 202610
      04 January 2025Project A01 June 202615
      04 January 2025Project A01 July 202615
      04 January 2025Project A01 August 202620
      04 January 2025Project A01 September 20265
      04 January 2025Project A01 October 202614
      04 January 2025Project A01 November 202622
      04 January 2025Project A01 December 202620
      04 January 2025Project A01 January 202714
      11 January 2025Project A01 January 20255
      11 January 2025Project A01 February 202511
      11 January 2025Project A01 March 202510
      11 January 2025Project A01 April 202515
      11 January 2025Project A01 May 202515
      11 January 2025Project A01 June 202520
      11 January 2025Project A01 July 202515
      11 January 2025Project A01 August 202520
      11 January 2025Project A01 September 202520
      11 January 2025Project A01 October 202511
      11 January 2025Project A01 November 20255
      11 January 2025Project A01 December 202514
      11 January 2025Project A01 January 202622
      11 January 2025Project A01 February 202620
      11 January 2025Project A01 March 202614
      11 January 2025Project A01 April 20265
      11 January 2025Project A01 May 202611
      11 January 2025Project A01 June 202610
      11 January 2025Project A01 July 20262
      11 January 2025Project A01 August 20263
      11 January 2025Project A01 September 20265
      11 January 2025Project A01 October 20264
      11 January 2025Project A01 November 202610
      11 January 2025Project A01 December 202615
      11 January 2025Project A01 January 202715
      11 April 2025Project A01 January 202520
      11 April 2025Project A01 February 20255
      11 April 2025Project A01 March 20256
      11 April 2025Project A01 April 20257
      11 April 2025Project A01 May 20258
      11 April 2025Project A01 June 20255
      11 April 2025Project A01 July 20259
      11 April 2025Project A01 August 202511
      11 April 2025Project A01 September 202512
      11 April 2025Project A01 October 202513
      11 April 2025Project A01 November 20252
      11 April 2025Project A01 December 20255
      11 April 2025Project A01 January 20262
      11 April 2025Project A01 February 20262
      11 April 2025Project A01 March 20262
      11 April 2025Project A01 April 202610
      11 April 2025Project A01 May 202615
      11 April 2025Project A01 June 202615
      11 April 2025Project A01 July 202620
      11 April 2025Project A01 August 20261
      11 April 2025Project A01 September 20265
      11 April 2025Project A01 October 202614
      11 April 2025Project A01 November 202622
      11 April 2025Project A01 December 202620
      11 April 2025Project A01 January 202714
      25 July 2025Project A01 January 20255
      25 July 2025Project A01 February 202511
      25 July 2025Project A01 March 202510
      25 July 2025Project A01 April 202520
      25 July 2025Project A01 May 20252
      25 July 2025Project A01 June 20252
      25 July 2025Project A01 July 20252
      25 July 2025Project A01 August 20252
      25 July 2025Project A01 September 202510
      25 July 2025Project A01 October 202515
      25 July 2025Project A01 November 202515
      25 July 2025Project A01 December 202520
      25 July 2025Project A01 January 20262
      25 July 2025Project A01 February 202614
      25 July 2025Project A01 March 202622
      25 July 2025Project A01 April 202620
      25 July 2025Project A01 May 202614
      25 July 2025Project A01 June 20265
      25 July 2025Project A01 July 202611
      25 July 2025Project A01 August 202610
      25 July 2025Project A01 September 20265
      25 July 2025Project A01 October 20263
      25 July 2025Project A01 November 20268
      25 July 2025Project A01 December 202699
      25 July 2025Project A01 January 20275
    • FBergamaschi's avatar
      FBergamaschi
      Icon for Super User rankSuper User
      1 year ago

      Thanks

       

      I need the timetable too and an image showing what exactly you want me to calculate, show me an example please

       

      Thanks

      • fjjpeeters1976's avatar
        fjjpeeters1976
        Icon for Helper III rankHelper III
        1 year ago

        Hi, any regular timetable will do, nothing fancy here. I cannot add the table as it exceeds the 20k characters. Regarding the outcome, please see below example. Basically I want to be able to compare one snapshot with another and see the changes. User can select which and how many snapshots they want to see. Both options below basically have the same issue, how can I create a dax measure that looks at the previous snapshot in the matrix. There is no defined logic in the snapshots, so I cannot use a dateadd function or an indexnumber to go to the next or previous snapshots.

         




  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    Thankyou, FBergamaschi, for your response.

    Hi fjjpeeters1976,

    We appreciate your question on the Microsoft Fabric Community Forum.

    Based on my understanding of the issue, please find attached the screenshots and a sample PBIX file that may assist in resolving the matter:

    We hope the information provided helps to resolve the issue. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

    Thank you.

    • fjjpeeters1976's avatar
      fjjpeeters1976
      Icon for Helper III rankHelper III
      1 year ago

      hi, thanks for your reply. It is not exactly what I needed but it got me into the right direction. I know figured out how to do it and using the ALLSELECT function was what made it work.

       

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi fjjpeeters1976,

    We would like to follow up and see whether the details we shared have resolved your problem.
    If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

    Thank you.

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi fjjpeeters1976,

    We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

    Thank you.

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi fjjpeeters1976,

    We wanted to see if the information we gave helped fix your problem. If you need more help, please feel free to contact the Microsoft Fabric community.

    Thank you.