Dax

[Sara_Hornstein]

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

[v-sihou-msft · Accepted solution]

@Sara_Hornstein

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,

[v-sihou-msft · Accepted solution]

@Sara_Hornstein

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,

[Sara_Hornstein · Accepted solution]

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,