Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 -
Solved! Go to Solution.
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.
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.
Exactly what I needed. Thank you so much!
Hi:
You could add a calculated column to Table.
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?
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
User | Count |
---|---|
52 | |
51 | |
20 | |
17 | |
16 |
User | Count |
---|---|
113 | |
46 | |
44 | |
28 | |
22 |