Forum Discussion

Umar's avatar
Umar
Regular Visitor
8 years ago
Solved

Need Help with Filter in Calculate

i am using below DAX expression to get the Account[DownloadDate] Per Quarter, i need to refine my result a little.

 

Evaluate
SummarizeColumns(
Dim_Calendar[FYQ],
"Test Measure",
CALCULATE
(
DISTINCTCOUNT(Dim_Accounts[Id]),
FILTER
(
Dim_Accounts,
Dim_Accounts[DownloadDate] >= FIRSTDATE(Dim_Calendar[Date])
&&
Dim_Accounts[DownloadDate] < NEXTDAY(LASTDATE(Dim_Calendar[Date]))
)
)
)
Order by Dim_Calendar[FYQ]

 

and here is the result i am getting 

 

Dim_Calendar[FYQ][Test Measure]
FY17Q33
FY17Q427
FY18Q120
FY18Q213
FY18Q38
FY18Q45

 

however i need to start it from the current quarter, which is FY17Q4 and want to get the same value next year, same quarter i.e. Example for "Account Id A"

      1. FY18Q1 Account Id A downloads Count = 1 
      2. FY18Q2-4 Count = 0
      3. FY19Q1 Count = 1 (should get this next year, next quarter.

Please help me out here.

Need Help
  • Umar's avatar
    Umar
    8 years ago

    Actually this solved my problem, may be i couldn't expalin the scenario properly.

    CountRows
(
Filter
(
ADDCOLUMNS
(
CALCULATETABLE(
SUMMARIZE
(
Dim_Accounts,
Dim_Accounts[Id],
Dim_Accounts[DownloadDate],
"DownloadedDay",LOOKUPVALUE(Dim_Calendar[FYD], Dim_Calendar[Date], Dim_Accounts[DownloadDate])
),
CALCULATETABLE(
Fact_Orders,
ALL(Dim_Calendar)
)
),
"Active",CONTAINS(VALUES(Dim_Calendar[FYD]), Dim_Calendar[FYD], [DownloadedDay])
),
[ACTIVE] = TRUE
&&
Dim_Accounts[DownloadDate] < MAX(Dim_Calendar[Date])
)
)

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Do you have an easy way to identify the first date of the current quarter?  If so could you write it into a measure?  If you did that, could you change your current measure to:

     

    VAR StartDate = [FirstDateOfQuarter]
RETURN
Evaluate
	SummarizeColumns
	(
		Dim_Calendar[FYQ],
		"Test Measure",
		CALCULATE
		(
			DISTINCTCOUNT(Dim_Accounts[Id]),
			FILTER
			(
				Dim_Accounts,
				Dim_Accounts[DownloadDate] >= FIRSTDATE(Dim_Calendar[Date])
				&&
				Dim_Accounts[DownloadDate] < NEXTDAY(LASTDATE(Dim_Calendar[Date]))
			),
			Dim_Calendar[Date] >= StartDate
		)
	)
Order by Dim_Calendar[FYQ]
    • Umar's avatar
      Umar
      Regular Visitor
      8 years ago

      Tried this, using 

      Var startDate = Calculate(min(dim_calendar[Date]), Filter(Dim_Calendar, Dim_Calendar[Is Current FYQ] = "Current FYQ" ) )

      and here is the result, it keep adding the previous count, taking the start date to be 01-10-2017 everytime.

       

      Dim_Calendar[FYQ][Test Measure]
      FY17Q427
      FY18Q147
      FY18Q260
      FY18Q368
      FY18Q473
      FY19Q173
      FY19Q273
      FY19Q373

       

      need to do something else, didn't figure out yet, anything else i can try.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Could you be overcomplicating the measure?  What is the use case for it?  Do you simply want to display it in a table visual?  If so what happens if you create a Table Visual and put Dim_Calander[FYQ] as the first value and the following measure as the 2nd?

         

         

        Test Measure = 
VAR StartDate = Calculate(min(dim_calendar[Date]), Dim_Calendar[Is Current FYQ] = "Current FYQ")
RETURN
Calculate(
	DISTINCTCOUNT(Dim_Accounts[Id]),
	Dim_Calendar[Date] >= StartDate
)

         

    • Umar's avatar
      Umar
      Regular Visitor
      8 years ago

      Actually this solved my problem, may be i couldn't expalin the scenario properly.

      CountRows
(
Filter
(
ADDCOLUMNS
(
CALCULATETABLE(
SUMMARIZE
(
Dim_Accounts,
Dim_Accounts[Id],
Dim_Accounts[DownloadDate],
"DownloadedDay",LOOKUPVALUE(Dim_Calendar[FYD], Dim_Calendar[Date], Dim_Accounts[DownloadDate])
),
CALCULATETABLE(
Fact_Orders,
ALL(Dim_Calendar)
)
),
"Active",CONTAINS(VALUES(Dim_Calendar[FYD]), Dim_Calendar[FYD], [DownloadedDay])
),
[ACTIVE] = TRUE
&&
Dim_Accounts[DownloadDate] < MAX(Dim_Calendar[Date])
)
)