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 have no clue why i get this result. It's very frustrated..
I tried to show running total progress by week.
i used this query for running total
and this is the relationship
this is the result graph.. not sure why i get the blank although i don't have blank in the database..!
any advice would be much appreciated.!
thanks,
CL
Solved! Go to Solution.
Hi, @colourfullife
Based on your description, I created data to reproduce your scenario.
Table:
Calendar(a calculated table):
Calendar = CALENDAR(DATE(2020,1,1),DATE(2020,3,31))
You may create a calculated column as below.
WeekNum = WEEKNUM('Calendar'[Date])
There is a one-to-one relationship between two tables.
Then you can create a measure as below.
Count Running total =
var _weeknum = SELECTEDVALUE('Calendar'[WeekNum])
var _date = SELECTEDVALUE('Calendar'[Date])
return
IF(
ISINSCOPE('Calendar'[Date]),
CALCULATE(
COUNT('Table'[Date]),
FILTER(
ALLSELECTED('Calendar'),
'Calendar'[Date]<=_date
)
),
CALCULATE(
COUNT('Table'[Date]),
FILTER(
ALLSELECTED('Calendar'),
'Calendar'[WeekNum]<=_weeknum
)
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @colourfullife
Based on your description, I created data to reproduce your scenario.
Table:
Calendar(a calculated table):
Calendar = CALENDAR(DATE(2020,1,1),DATE(2020,3,31))
You may create a calculated column as below.
WeekNum = WEEKNUM('Calendar'[Date])
There is a one-to-one relationship between two tables.
Then you can create a measure as below.
Count Running total =
var _weeknum = SELECTEDVALUE('Calendar'[WeekNum])
var _date = SELECTEDVALUE('Calendar'[Date])
return
IF(
ISINSCOPE('Calendar'[Date]),
CALCULATE(
COUNT('Table'[Date]),
FILTER(
ALLSELECTED('Calendar'),
'Calendar'[Date]<=_date
)
),
CALCULATE(
COUNT('Table'[Date]),
FILTER(
ALLSELECTED('Calendar'),
'Calendar'[WeekNum]<=_weeknum
)
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Try with a date calendar. like
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,max(dateadd(date[date]),-1,year))))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))
hello @amitchandak
thanks for your reply!
can you tell me a bit more detail? i am pretty new in PBI.. i am not sure where i need those codes?
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 |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |