Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Community,
I am connected to a live dataset where I can't create calculated columns or go to the power query, I have a column date and I want to extract the month/Year from it.
Is there any possibilty to create measure to extract this ?
Thanks in advance
Hi @Anonymous ,
As measure calculated by the context, in the formula, the context is date, if you put date in the visual, the formula will return the current date in the same row, if you don't put date in the visual, the formula will return the max date in the table.
My solution is put date in the visual, and create another measure to sum the value.
SUM =
SUMX (
FILTER (
ALL ( 'Table' ),
YEAR ( 'Table'[Date] ) = YEAR ( MAX ( 'Table'[Date] ) )
&& MONTH ( 'Table'[Date] ) = MONTH ( MAX ( 'Table'[Date] ) )
),
'Table'[Value]
)
Get the correct result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @v-yanjiang-msft
Thanks for your effort. Unfortenatly this is not the expected result, I made some modification on your pbix
file, I added a number column and another date in 2020,
what I want to display is a table showing the sum of value per number and date for example:
Number Date SUM
1002 FEB 2020 55
1002 FEB 2022 15
1003 FEB 2020 40
1003 FEB 2022 20
I hope I was clear
Thanks in advance 🙂
Hi @Anonymous ,
Does your expected result based on the data in the screen shot? In your screen shot, in my understanding, you want to get:
Num Date Sum
1002 2022-01 6
1003 2022-02 15
1002 2020-01 11
1003 2020-01 15
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sorry @v-yanjiang-msft , but the measure didn't work because it's direct query 😞
Hi @Anonymous ,
I modify the formula based on your sample data, you can have a try.
SUM =
SUMX (
FILTER (
ALL ( 'Table' ),
YEAR ( 'Table'[Date] ) = YEAR ( MAX ( 'Table'[Date] ) )
&& MONTH ( 'Table'[Date] ) = MONTH ( MAX ( 'Table'[Date] ) )
&& 'Table'[Number] = MAX ( 'Table'[Number] )
),
'Table'[Value]
)
Get the result.
I attach the sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Fowmy Thanks for you help, I have 3 columns raw material number, Quantity and date, I want when selecting these 3 columns on a table, see the sum of quantity per material number per date for example:
I have this data, I want the sum of quantity for 1002 on february, mars...
@Anonymous
You can use a measure to extract the Month Year as follows:
MonthYear = format( MAX('Date'[Date]) , "Mmm yyyy")
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks for you response, but when I add a calculation with this measure it takes always the latest month as seen here :
@Anonymous
May I know, based on the screenshot above, what your expected result for Month and Year ?
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |