Forum Discussion
CUMULATIVE COUNT FROM BEGINNING OF TIME TO RANDOM SELECTED DATE
Could i ask for your idea regarding this scenario i have?
Hi jiji ,
I created some data:
Here are the steps you can follow：
1. Create calculated table.
Table 2 = CALENDAR(MIN('Table'[date]),MAX('Table'[date]))2. Create measure.
count = IF( ISFILTERED('Table 2'[Year])&& ISFILTERED('Table 2'[Month]), CALCULATE(COUNT('Table'[date]),FILTER(ALL('Table'),'Table'[date]<= DATE(SELECTEDVALUE('Table 2'[Year]),SELECTEDVALUE('Table 2'[Month]),1))), IF( ISFILTERED('Table 2'[Year]),CALCULATE(COUNT('Table'[date]),FILTER(ALL('Table'),'Table'[year]<=SELECTEDVALUE('Table 2'[Year]))), 0))3. Result:
When only the year is selected:
Select year and month:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
Whitewater100Solution Sage
Hi:
You can start off with a basic measure for quantity.
Qty = SUM(Table[Qty])
Lifetime Value Qty = CALCULATE([Qty], FILTER(ALL(DATES), Dates[Date] <= MAX(Dates[Date]))
* need a date table marked as such and with relationship to your facttable.
Here is some Date Table Code you can use if needed Modeling>NEW TABLE:
Dates =
-- Specify a start date and end date
VAR StartDate = Date(2021,1,1)
VAR EndDate = Today() + 243
VAR FiscalMonthEnd = 12
-- Generate a base table of dates
VAR BaseTable = Calendar(StartDate, EndDate)
-- Add the Year for each individual date
VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))
-- Add the calendar month and other month related data for each date
VAR Months = ADDCOLUMNS(
Years,
"Month",MONTH([Date]),
“Month Name” ,FORMAT([Date], “MMM”),
"Year and Month Number",FORMAT([Date],"YYYY-MM"),
"Year and Month Name",FORMAT([Date],"YYYY-MMM"),
"Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),
"Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),
IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))
)
-- Add the Quarter and other quarter related data for each date
VAR Quarters = ADDCOLUMNS(
Months,
"Quarter",ROUNDUP(MONTH([Date])/3,0),
"Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))
-- Add the Day and other day related data for each date
VAR Days = ADDCOLUMNS(
Quarters,
"Day",DAY([Date]),
"Day Name",FORMAT([Date],"DDDD"),
"Day Of Week",WEEKDAY([Date]),
"Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)
-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date
VAR Weeks = ADDCOLUMNS(
Days,
"Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,
"Week of Year (Sunday)",WEEKNUM([Date],1),
"Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))
-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.
var WorkingDays = ADDCOLUMNS(
Weeks,
"Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})
RETURN WorkingDays
I hope this helps and solves question. Thanks
Hi jiji ,
I created some data:
Here are the steps you can follow：
1. Create calculated table.
Table 2 = CALENDAR(MIN('Table'[date]),MAX('Table'[date]))2. Create measure.
count = IF( ISFILTERED('Table 2'[Year])&& ISFILTERED('Table 2'[Month]), CALCULATE(COUNT('Table'[date]),FILTER(ALL('Table'),'Table'[date]<= DATE(SELECTEDVALUE('Table 2'[Year]),SELECTEDVALUE('Table 2'[Month]),1))), IF( ISFILTERED('Table 2'[Year]),CALCULATE(COUNT('Table'[date]),FILTER(ALL('Table'),'Table'[year]<=SELECTEDVALUE('Table 2'[Year]))), 0))3. Result:
When only the year is selected:
Select year and month:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly