Forum Discussion

Matt_Mohawk's avatar
Matt_Mohawk
Helper II
3 years ago

Count Jobs within Dates Selected

Hi,   I have a list of jobs that have entered dates. I have a date table connected to it as well. The date table is connected to the add-in called ChicletSlicer. When I select the year and the mont...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

Hi,

Ensure that you have a Calendar Table with calculated column formulas for year, Month name and Month number.  Sort the Month name column by the Month number.  Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table.  to your visual, drag year and Month name from the Calendar Table.  Write these measures

Date fo first interaction = calculate(min(Data[Transaction date]),datesbetween(calendar[date],minx(all(calendar),calendar[Date]),max(calendar[date])))

New customers = countrows(filter(values(data[customer code]),[Date fo first interaction]>=min(calendar[date])))

Drag the second measure to a card visual.

Hope this helps.

Matt_Mohawk's avatar
Matt_Mohawk
Helper II
3 years ago

I appreciate the formula that you provided but for some reason it doesn't work correctly. Can you include what my calendar formula should look like? This is what it looks like on my side.

 

Date =
ADDCOLUMNS (
CALENDAR (DATE(2017,1,1), DATE(2032,12,31)),
"DateAsInteger", FORMAT ( [Date], "MMDDYY" ),
"Year",YEAR([Date]),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    Create the Calendar table with the following formula

    Calendar = calendar(DATE(2017,1,1), DATE(2032,12,31))

    Write these calculated column formulas

    Year = year(Calendar[Date])

    Month name = format(calendar[Date],"mmmm")

    Month number = month(calendar[Date])

    Sort the Month name column by the Month number.  to your visual, drag Year and Month name from the Calendar Table.

    Hope this helps.