Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I want to build a cumulative graph of the Items column. The problem is that currently, I would like if the date does not exist in the Date column, to still continue the cumulative calculation until today's date. Currently, the cumulative will display the value 2 for the date 2024-03-20, then continue with the date 2024-03-22 with the value 3. So, there are not the date 2024-03-21. But, I want that at 2024-03-21, it will return the value 2.
THANKS.
It must give sommething like this:
Solved! Go to Solution.
Good Morning,
do You have a DateTable ?
if not you can create one by using this code:
DIM_Datetable =
VAR calender =
CALENDAR(
Date( 2020,01,01 -- FirstDate
),
DATE(
2030,01,01 -- Last Date
)
)
RETURN
ADDCOLUMNS(
calender,
Year,YEAR([Date]),
Quarter, CONCATENATE(Q,QUARTER([Date])),
Year Month, YEAR([Date]) & - & FORMAT(MONTH([Date]), 00),
Month, MONTH([Date]),
Month Name, FORMAT([Date], MMMM),
Day of year, DATEDIFF(DATE(YEAR([Date]),1,1),[Date],DAY)+1,
Day of Month, DAY([Date]),
Day of week, WEEKDAY([Date],2),
Day Name, FORMAT([Date], DDDD),
Weeknum, FORMAT(WEEKNUM([Date],2),00),
Year Weeknum, YEAR([Date]) & FORMAT(WEEKNUM([Date],2),00),
Year Quarter, YEAR([Date]) & - & QUARTER([Date])
)
This code will create a date table including the dates you specify. After creating this table You can connect it with your fact table by a one to many relationship then use the date table instead of your date column in your graph.
Let me know if this was useful!
Hi @game1
There are two possibilities. The first one will require a date table as @DAX_Machine suggested. When the data model is set up correctly you can use the Date from the Date table in the visual and then add this measure
RunningTotal =
VAR MaxDate =
MAX ( DIM_Date[Date] )
RETURN
CALCULATE (
COUNTROWS(Table),
KEEPFILTERS ( 'Table'[Date] <= MaxDate
&& 'Table'[Date] >= MaxDate,
ALL ( DIM_Date )
If a Date table isn't an option you can try @Greg_Deckler solution
The Case for No CALCULATE (linkedin.com)
RunningTotal =
VAR __Date = MAX('Table'[Date])
VAR __Table = FILTER(ALLSELECTED('Table'),[Date] <= __Date)
VAR __Result = COUNTX(__Table,[Items])
RETURN
__Result
Hope this helps
Joe
If you found my answer helpful and it solved your issue, please accept as solution
Proud to be a Super User! | |
Date tables help! Learn more
Hi @game1
There are two possibilities. The first one will require a date table as @DAX_Machine suggested. When the data model is set up correctly you can use the Date from the Date table in the visual and then add this measure
RunningTotal =
VAR MaxDate =
MAX ( DIM_Date[Date] )
RETURN
CALCULATE (
COUNTROWS(Table),
KEEPFILTERS ( 'Table'[Date] <= MaxDate
&& 'Table'[Date] >= MaxDate,
ALL ( DIM_Date )
If a Date table isn't an option you can try @Greg_Deckler solution
The Case for No CALCULATE (linkedin.com)
RunningTotal =
VAR __Date = MAX('Table'[Date])
VAR __Table = FILTER(ALLSELECTED('Table'),[Date] <= __Date)
VAR __Result = COUNTX(__Table,[Items])
RETURN
__Result
Hope this helps
Joe
If you found my answer helpful and it solved your issue, please accept as solution
Proud to be a Super User! | |
Date tables help! Learn more
Good Morning,
do You have a DateTable ?
if not you can create one by using this code:
DIM_Datetable =
VAR calender =
CALENDAR(
Date( 2020,01,01 -- FirstDate
),
DATE(
2030,01,01 -- Last Date
)
)
RETURN
ADDCOLUMNS(
calender,
Year,YEAR([Date]),
Quarter, CONCATENATE(Q,QUARTER([Date])),
Year Month, YEAR([Date]) & - & FORMAT(MONTH([Date]), 00),
Month, MONTH([Date]),
Month Name, FORMAT([Date], MMMM),
Day of year, DATEDIFF(DATE(YEAR([Date]),1,1),[Date],DAY)+1,
Day of Month, DAY([Date]),
Day of week, WEEKDAY([Date],2),
Day Name, FORMAT([Date], DDDD),
Weeknum, FORMAT(WEEKNUM([Date],2),00),
Year Weeknum, YEAR([Date]) & FORMAT(WEEKNUM([Date],2),00),
Year Quarter, YEAR([Date]) & - & QUARTER([Date])
)
This code will create a date table including the dates you specify. After creating this table You can connect it with your fact table by a one to many relationship then use the date table instead of your date column in your graph.
Let me know if this was useful!
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
9 | |
7 |