"Save" away result to compare with the new run of data

[Wresen]

Hi and thanks for reading this.

(and sorry for the bad subject.)

My question if it is possible to save the calculted resualt in the report so next time i run the report with new data i have the old result in the report so i can compair the new data result with the old.

ex.

As you probebly understand i the data i have is much more "complicated" than Apple sales (there is 3 x's in the calculation and the changes every day)

/Thanks so much 

[amitchandak]

@Wresen , Try with date table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))
MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))
	


diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[Wresen]

Hi @amitchandak 

Thanks so much , but i think i have not explained well.

I fell abit bad for that since you made a great post.

So what i want is to save away the total number of sold Apples (3) from 201903 so i can compair the sales i made 201904 with 201903.
In the data 201904 i Only see 201904 data , 201903 data is gone , thats why i want to "save" the resulat in the report when i loaded the data 201903.

[v-lionel-msft · Accepted solution]

Hi @Wresen ,

Are you saying that every update to your dataset will overwrite the previous data? If yes, you can't save the last result dynamically, you can only save the last result in the text box manually.

But if every time new data is appended to the original data, you can do like this.

1. Create a calculated column.

_Rank = 
RANKX(
    Sheet1,
    Sheet1[Month],
    , ASC, Dense
) 

2. Create a measure.

Sold of last month = 
VAR x =  
CALCULATE(
    SUM(Sheet1[Sold]),
    FILTER(
        ALL(Sheet1),
        Sheet1[_Rank] = MAX(Sheet1[_Rank]) - 1
    )
)
RETURN
x

Best regards,
Lionel Chen

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

[Wresen]

Thanks so much @v-lionel-msft 

You answerd my question with:

Are you saying that every update to your dataset will overwrite the previous data? If yes, you can't save the last result dynamically, you can only save the last result in the text box manually.

Thanks so much @v-lionel-msft  and @amitchandak  for all the help here.