question

Shinka-3536 avatar image
0 Votes"
Shinka-3536 asked Lz-3068 commented

Keeping the value of January in a DAX measure

Hi all,

I've got a certain set of values for each month of the year and need to find the difference with the previous month. I've mananged to do that with the DATEADD function, but the problem is that I need it to respect the value of january with substracting the value of december.

Please find an example here:

https://1drv.ms/x/s!AqEBYAVXS3ejgqVCRbghi6iUKMfYCg?e=A8XlPB

The TABLE is a sample of my table, the EXPECTED RESULT is what I want to achieve, the RESULT is what I've managed. In the same spreadsheet you'll fine the DAX formula used to create the meassure.

How may I adapt it so that I may achieve the EXPECTED RESULT?

Thanks,

office-excel-itprosql-server-analysis-services
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@Shinka-3536,
Welcome ro Q&A forum!
You may need to add the IF function to the formula. Please check whether the following replies are helpful to you.
Any updates, please let us know.

0 Votes 0 ·

I'm trying, just can't seem to find thr proper syntax

0 Votes 0 ·
Lz-3068 avatar image
0 Votes"
Lz-3068 answered Lz-3068 commented

Hi @Shinka-3536

With data in Table1. Added a Date table (named Calendar) + established relationship between the 2 tables on [Date]

PIC

Measure Total_Value:
=SUM(Table1[Value])

Measure myTotal:
111249-revisedmeasure.png

Alternatively to avoid nested IFs:
111384-alternativemeasure.png

Corresponding sample avail. here
NB: I'm not really a DAX man :( so any follow-up question will likely not be answered by me



· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AlexeiStoyanovsky

If you don't mind & to help me in my learning curve re. PP. Does what I suggested make sense to you? Any comment?
Thanks

0 Votes 0 ·

Went for first option, worked greatly!

Thanks,

0 Votes 0 ·

@Shinka-3536. Glad I could help & Thanks for posting back. Nice WE...

0 Votes 0 ·
AlexeiStoyanovsky avatar image
0 Votes"
AlexeiStoyanovsky answered Shinka-3536 commented

You need to code your January exception to the general logic into the measure's DAX, of course. Judging by the pivots, you seem to have a month name column in your calendar table, so for the pivot layout in your example you could add e.g. an IF ( SELECTEDVALUE ( 'Calendar'[Month name] ) = "January", <simple sum>, <difference to prev> ). Note that this will play merry hell with calculations at anything other than month granularity, but then, you original difference to prev month is as susceptible.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Can't use SELECTEDVALUE, don't know why

0 Votes 0 ·