TotalQTD if one month is missing

[Anonymous]

Hi ,

I have sales value for jan and feb but no values for march. In such case how to get totalqtd value if user select March from slicer.

Table

Name      Period             Sales

abc          01/01/2022     100

abc           02/02/2022     100

Created calendar table starting date is 1/1/2022 and created a relationship with period.

Below query not giving any value if user select March from slicer(Calendar[Date])

totalqtd = TOTALQTD(sum('qtd'[sales]),'Calendar'[Date],ALL('calendar'[Date]))

Expectted result when user select March

Name  Sales   qtdSales

abc                  200

Thanks in advance.

[Greg_Deckler]

@Anonymous Create a Quarter column in your Calendar table Quarter = QUARTER([Date]). Then you can do this:

qtdSales =
  VAR __Year = MAX('Calendar'[Year])
  VAR __Quarter = MAX('Calendar'[Quarter])
  VAR __Dates = SELECTCOLUMNS(FILTER(ALL('Calendar'),[Year] = __Year && __Quarter = [Quarter]),"__Date",[Date])
  VAR __Table = FILTER('qtd',[Period] IN __Dates)
RETURN
  SUMX(__Table,[Sales])

Dont' use TI functions. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

[Anonymous]

For March I am not getting any data.