Avg LTD dax logic

[Anonymous]

Hi,

 i am not able to get the below output 'Avg LTD' - below screenshot

'LTD Qty' and 'all period count' columns should not depend on the slicer selection made.

ex- slicer period type=month period name=apr and may

LTD Qty = sum of all qty till date (may+apr+mar+feb=60 for group A), sum of all qty till date (may+apr+jan=80 for group B)

'all period count' - 4 for Group A(may,apr,mar,feb),  3 for Group B(may,apr,jan), 

should show count of all period name which has data for the respective group and period type slicer.

data is avilable below. need your help for solving this

Period Key	Period Type	Period Name	Date	Group	Qty
1	Month	May	12-May	A	20
1	Month	May	15-May	A	5
1	Month	Apr	7-Apr	A	15
1	Month	Apr	15-Apr	A	10
1	Month	Mar	23-Mar	A	5
1	Month	Feb	11-Feb	A	5
1	Month	May	15-May	B	20
1	Month	Apr	10-Apr	B	30
1	Month	Jan	14-Jan	B	30
2	Qtr	Q2	12-May	A	20
2	Qtr	Q2	15-May	A	5
2	Qtr	Q2	7-Apr	A	15
2	Qtr	Q2	15-Apr	A	10
2	Qtr	Q1	23-Mar	A	5
2	Qtr	Q1	11-Feb	A	5
2	Qtr	Q2	15-May	B	20
2	Qtr	Q2	10-Apr	B	30
2	Qtr	Q1	14-Jan	B	30

Thanks

Raj

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

Please try like this:

LTD Qty =
CALCULATE (
    SUM ( 'Table'[Qty] ),
    ALLEXCEPT ( 'Table', 'Table'[Group], 'Table'[Period Type] )
)


all period count =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Period Name] ),
    ALLEXCEPT ( 'Table', 'Table'[Group], 'Table'[Period Type] )
)

AVG = DIVIDE([LTD Qty],[all period count])

[amitchandak]

@Anonymous ,  Try like example given below with date table

Cumm Sales =

Var _max = maxx(allselected(date),date[date])

return

CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=_max ))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.