Forum Discussion
Dax
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
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,
2 Replies
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,