topic Re: Make a Column using DAX that fetches value for only first date of month in DAX Commands and Tips

Make a Column using DAX that fetches value for only first date of month

tintinsherlock — Tue, 10 May 2022 12:06:44 GMT

Hi everyone,

Sorry if the title is not very clear, so I'll try to detail the ask/request in detail here.
I have a dataset/table "HelloWorld" that looks like below

Date	Type	Value
2022-04-01	A	100
2022-04-01	A	50
2022-04-01	B	30
2022-04-01	C	10
2022-04-02	B	20
2022-04-02	C	10
2022-05-01	A	200
2022-05-01	A	100
2022-05-01	B	50
2022-05-01	C	10

I want a calculated column which will be fed to a card, and a Date filter/slicer drop-down visual will be connected to that. I want the calculated column to show -
For any selected date of the month, the output value should be SUM of all Value for 1st of that month where Type = A.

So if the Date is selected as 2022-04-01, the Value shown on the card should be 150 (100 + 50 for two Type = A entries of 2022-04-01).
If the date is 2022-04-01 (or any date in 2022-04) the value should still be 150.

If the Date selected is now changed 2022-05-01, the value shown on the card should be 300 (200 + 100 for two Type = A entries of 2022-05-01), and same for any date selected in 2022-05. And so on.

I tried the following piece of code, which gave me erroneous result -

VAR startMonth = STARTOFMONTH(HelloWorld[Date])

RETURN CALCULATE(SUM(HelloWorld[Value]), FILTER (HelloWorld,HelloWorld[Date] == startMonth),FILTER (HelloWorld, HelloWorld[Type] == "A"))

Any help will be hugely appreciated. Thanks in advance.

Re: Make a Column using DAX that fetches value for only first date of month

Whitewater100 — Tue, 10 May 2022 12:26:30 GMT

Hi:

You could add a calculated column to Table.

First Month Value =

CALCULATE(SUM('Table'[Value]),

'Table'[Type] = EARLIER('Table'[Type]),ALLEXCEPT('Table','Table'[Date]))

If you set date slicer to first of month the answer should be ok. I hope this helps!

Re: Make a Column using DAX that fetches value for only first date of month

tintinsherlock — Tue, 10 May 2022 12:30:54 GMT

Hey Whitewater100 That's the thing. I want the value of the all the days of the month to be the same as the first of the month.
So 150 should be the value for all days of 2022-04 and 300 for 2022-05 and so on.
So even if someone selects the other day on the slicer, they see this same value.
(Context - this value represents a monthly count so no it should show this value no matter what date)
Is there any other solution you can suggest?

Re: Make a Column using DAX that fetches value for only first date of month

Whitewater100 — Tue, 10 May 2022 13:20:47 GMT

Hi:

Can you check this out? Please see page 2. (I combine replies sometimes)

https://drive.google.com/file/d/1ZAjWFdUWn39_GSDDYaxck8oLgzPNd1-_/view?usp=sharing

Re: Make a Column using DAX that fetches value for only first date of month

v-yalanwu-msft — Fri, 13 May 2022 06:15:20 GMT

Hi， tintinsherlock ；

You could create a measure.

First Value = VAR _startMonth = STARTOFMONTH('Table'[Date]) RETURN CALCULATE(SUM([Value]),FILTER(ALL('Table'),[Date]=_startMonth&&[Type]="A"))

The final shown :

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Make a Column using DAX that fetches value for only first date of month

tintinsherlock — Fri, 13 May 2022 11:39:59 GMT

Exactly what I needed. Thank you so much!