Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hi
I am trying to import a table the contains last 4 weeks of data. The planning week column is a String column and has the yyyy-ww format. Is there a way to convert this column to an integer YYYY-WW column in DAX Studio and import the last 4 weeks of data?
Note: No date column in the table
Solved! Go to Solution.
So you have data in a dataset within powerbi/SSAS and want to a query to return the last four weeks data?
Are you able to create a date table in the model:
Calendar =
ADDCOLUMNS (
CALENDAR( DATE (2021, 01, 01), DATE (2021, 12, 13) ),
"Year-Week", YEAR([Date]) & "-" & WEEKNUM([Date])
)
You can then join that table to your table using a many many relationship between Year-Week and your planning week. (Set to filter from Calendar to your table).
At that point you can use normal DAX date filtering. Can send example if you need.
If you can't add a date table to the model let me know and I'll think again.
Ok I understand now and have given it some thought. You can do the whole thing in DAX studio and create a section of a date table on the fly...
DEFINE
TABLE
TempCalendar =
VAR CurrentDate = TODAY()
VAR StartOfThisWeek = CurrentDate - WEEKDAY(CurrentDate, 2) + 1
VAR StartOfPeriod = StartOfThisWeek - 21
RETURN
ADDCOLUMNS (
CALENDAR( StartOfPeriod, StartOfThisWeek ),
"Year-Week", YEAR([Date]) & "-" & WEEKNUM([Date])
)
EVALUATE
CALCULATETABLE (
WeekTable,
TREATAS ( VALUES ( TempCalendar[Year-Week] ), WeekTable[Year-Week] )
)
You might have to tweak some of the numbers at the top depending on whether you want to include the current week.
I want to get this data by writing a DAX query in DAX studio rather than in a power query.
I wanna import only the last 4 weeks of data from the DB.
So you have data in a dataset within powerbi/SSAS and want to a query to return the last four weeks data?
Are you able to create a date table in the model:
Calendar =
ADDCOLUMNS (
CALENDAR( DATE (2021, 01, 01), DATE (2021, 12, 13) ),
"Year-Week", YEAR([Date]) & "-" & WEEKNUM([Date])
)
You can then join that table to your table using a many many relationship between Year-Week and your planning week. (Set to filter from Calendar to your table).
At that point you can use normal DAX date filtering. Can send example if you need.
If you can't add a date table to the model let me know and I'll think again.
Hi @bcdobbs
I imported the WorkOrderLabourS table from the Analysis Services DB by writing the following query.
EVALUATE
FILTER(WorkOrderLabourS, left(WorkOrderLabourS[Planning Week],4)="2020" ||left(WorkOrderLabourS[Planning Week],4)="2021" )
But since the file size is too huge I am looking for importing the last 4 weeks of data from the DB into the power bi by writing a DAX query in Dax studio.
I am looking for something like this,
EVALUATE
FILTER(WorkOrderLabourS, IN LAST 4 CALENDAR WEEKS BASED OF Planning Week COLUMN)
Thank you
Rohini
Ok I understand now and have given it some thought. You can do the whole thing in DAX studio and create a section of a date table on the fly...
DEFINE
TABLE
TempCalendar =
VAR CurrentDate = TODAY()
VAR StartOfThisWeek = CurrentDate - WEEKDAY(CurrentDate, 2) + 1
VAR StartOfPeriod = StartOfThisWeek - 21
RETURN
ADDCOLUMNS (
CALENDAR( StartOfPeriod, StartOfThisWeek ),
"Year-Week", YEAR([Date]) & "-" & WEEKNUM([Date])
)
EVALUATE
CALCULATETABLE (
WeekTable,
TREATAS ( VALUES ( TempCalendar[Year-Week] ), WeekTable[Year-Week] )
)
You might have to tweak some of the numbers at the top depending on whether you want to include the current week.
Hi @bcdobbs
I was testing out this code as the new data is available now for the year 2022. However, I get this error with this code.
DEFINE
TABLE
TempCalendar =
VAR CurrentDate = TODAY()
VAR StartOfThisWeek = CurrentDate - WEEKDAY(CurrentDate, 2) + 1
VAR StartOfPeriod = StartOfThisWeek - 21
RETURN
ADDCOLUMNS (
CALENDAR( StartOfPeriod, StartOfThisWeek ),
"Year-Week", YEAR(TODAY()) & "-" & WEEKNUM(TODAY())
)
EVALUATE
CALCULATETABLE (
ProjectCost,
TREATAS ( VALUES ( TempCalendar[Year-Week] ), ProjectCost[Planning Week PC ] )
)
Error:
Query (18, 22) Table variable 'TempCalendar' cannot be used in current context because a base table is expected.
Could you please help me understand this issue.
Thank You
desai69
Hi @bcdobbs
Thank you so much it worked.
Could please explain to me the logic behind
Filter2 = CurrentYear & "-" & (CurrentWeek - 1)
is it the current week minus 1?
Thank you once again
See my latest post it's a much cleaner way of doing it that deals with year changes as well.
In answer to my original solution yes it's the current week number take away 1. That works fine until one of your weeks is the start of january.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!