How to determine a change in value for an attribute over a time period

[susan_d_10]

I am very new to this, so i am hoping my explanation makes sense.       I am trying to show the monthly variance of a part attribute.    This attribute is not a measure but just a number and there can be daily records in the cube.      I am using Live Connection   For EOM processing a flag is set to indicate that the value on the last day of the month against the part is that month's value.    I am trying to get something like this below, so i can show any variances.    I have very limited experience with DAX and tried PREVIOUSMONTH but this will only let me SUM the number value.   I need only the one value that has the EOM processing flag against it.   Any ideas would be great, so far i have spent a number of days on this.

Part            Current Mth        Previous Mth      % Diff

widget 1     5.5                       4.5                       18%

[Greg_Deckler]

Hmm, if this is a LIVE connection, I am not certain that time intelligence functions are supported. I know they are not for Direct Query and I *think* this also covers Live connections. If that is indeed the case, See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[amitchandak · Accepted solution]

If you have a calendar, datesmtd and toyalmtd can help. Option 2 and 3, is the one work for you

// MTD and LMTD
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))))

// MTD and LMTD, if you have month end filter

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]),Sales[EOM]=1)
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)),Sales[EOM]=1)
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))),Sales[EOM]=1)

// MTD and LMTD, filter for month end
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),filter('Date','Date'[Date]=endofmonth('Date'[Date])))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),filter('Date','Date'[Date]=endofmonth(dateadd('Date'[Date],-1,MONTH))))

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/

[susan_d_10]

Thank you for this.  As soon as i use CALCULATE(SUM.....) it is summing up the values for the entire month.    there is a part price every day of the month the only value that i need is the price that has EOM snapshot flag set to Y.   I am very new at this and trying to work out how to attach an excel file.   But sample below. 

Snapshot Date	EOM Snapshot Flag	Part	Price
31/01/2020	Y	Widget1	11		Sample
23/01/2020	N	Widget1	11.75		Jan
15/01/2020	N	Widget1	11.75		Part	Curr Mth	Prev Mth	Diff
10/01/2020	N	Widget1	11.25			Jan	Dec
9/01/2020	N	Widget1	11.25		Widget 1	11	11.5	4.50%
8/02/2020	N	Widget1	11.25		Widget2	7.75	7	9.60%
7/02/2020	N	Widget1	11.25
6/02/2020	N	Widget1	11.25
5/02/2020	N	Widget1	11.25
4/02/2020	N	Widget1	11.25
3/02/2020	N	Widget1	11.5
2/02/2020	N	Widget1	11.5
1/02/2020	N	Widget1	11.5
31/12/2019	Y	Widget1	11.5
25/02/2020	N	Widget2	7

[amitchandak]

@susan_d_10 

I think sample do not have enough data for Widget two. Please find solution attached(Link below signature)

[susan_d_10]

Thank you very much.    This is so close.   It works on the data provided and that may be my mistake.   On our data the MAX is making it return the highest number, not the number that was the last at EOM.    I will keep working with these and i am sure i will crack it.

[susan_d_10]

I have tried all of these but to no avail.     No matter what i do with the CALCULATE i immediately get every day's price, instead of only the one that is the EOM figure