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.
Hi,
I am a beginner here in power BI, and maybe my question is very simple but I can´t solve it.
I have an excell sheet, and I have a column with dates, I want to organize that column in individual graphs for exemple: shipped today, yesterday and this week. I manage to sort out today and yesterday, but I have no idea what command should I write for the weekly graph, I tried: Shipped_week = COUNTROWS(filter(Sheet1,Sheet1[Shipment Date]=WEEKDAY,(DATE(),YEAR(2017),MONTH(4),DAY(18)()) (which is a mistake I know but I have no idea what to write). And I want all those values to be update every week/day.
Many thanksin advance,
Sara
Solved! Go to Solution.
If you want to calculate weekly data, you can add a week number column in your date table like below:
WeekNumber = WEEKNUM('Table'[Date],21)
Then you just need to CALCULATE() group on week number using ALLEXCEPT().
Measure = CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[WeekNumber] ) )
Rergards,
Hi Simon,
Thank you so much for that! I will try it.
In the meantime I think I manage to solve it, but it seems I went for a much more complicated way...
This is what I did for the "today" and "yesterday"
shipped_today = COUNTROWS(filter(Sheet1,Sheet1[Shipment Date]=TODAY()))
shipped_yesterday = COUNTROWS(filter(Sheet1,Sheet1[Shipment Date]=TODAY()-1))
And for the weekdays:
I created a column myweekday2 = if(Sheet1[Shipment Date]>today()-WEEKDAY(today()), WEEKDAY(Sheet1[Shipment Date]), 0)
Then I create a table for date and weekdays: Date =
ADDCOLUMNS (
CALENDAR (DATE(2017,04,17), DATE(2017,04,21)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Weekday", WEEKDAY( ( [Date] )
))
And then I made a relationship between sheet1 and date table.
And finally I could creat the graphs accordingly...I will also try what you suggested, maybe I will get the same but from a more easy way!
Many thanks!
Regards,
If you want to calculate weekly data, you can add a week number column in your date table like below:
WeekNumber = WEEKNUM('Table'[Date],21)
Then you just need to CALCULATE() group on week number using ALLEXCEPT().
Measure = CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[WeekNumber] ) )
Rergards,
Hi Simon,
Thank you so much for that! I will try it.
In the meantime I think I manage to solve it, but it seems I went for a much more complicated way...
This is what I did for the "today" and "yesterday"
shipped_today = COUNTROWS(filter(Sheet1,Sheet1[Shipment Date]=TODAY()))
shipped_yesterday = COUNTROWS(filter(Sheet1,Sheet1[Shipment Date]=TODAY()-1))
And for the weekdays:
I created a column myweekday2 = if(Sheet1[Shipment Date]>today()-WEEKDAY(today()), WEEKDAY(Sheet1[Shipment Date]), 0)
Then I create a table for date and weekdays: Date =
ADDCOLUMNS (
CALENDAR (DATE(2017,04,17), DATE(2017,04,21)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Weekday", WEEKDAY( ( [Date] )
))
And then I made a relationship between sheet1 and date table.
And finally I could creat the graphs accordingly...I will also try what you suggested, maybe I will get the same but from a more easy way!
Many thanks!
Regards,
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 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |