Measure to get initial exports of Premium Red and Pinot Grigio in terms of volume

[gbarr12345]

Hi there,

I have a query on a measure that someone requested from me.

I've been asked to create a measure to get initial exports of Premium Red and Pinot Grigio in terms of volume for the first 12 months versus the volume of depletions out.

The sample file I have attached below is the data I was provided with by the user with just a small few tweaks of the data from me due to confidentiality.

https://docs.google.com/spreadsheets/d/13VHY8tQP0y7xODUhuGRUsTShfUA1Gtci/edit?usp=drive_link&ouid=11...

Can anyone help me with creating this measure? I tried a measure and it doesn't seem to have worked. It could be that I'm not using the correct fields. Measure is below also.

Premium Red & Pinot Grigio Initial Exports =

VAR StartDate = CALCULATE( MIN( Export Sales Data[Date]), ALL( Export Sales Data ))

VAR EndDate = EDATE( StartDate, 12)

RETURN

CALCULATE(

    SUM( Export Sales Data[Avg 9LE]),

    Export Sales Data[Description] IN {"Premium Red Blend ", "Pinot Grigio" },

    Export Sales Data[Date] >= StartDate ,

    Export Sales Data[Date] < EndDate

)

[Anonymous · Accepted solution]

Thanks for the reply from @amitchandak , please allow me to provide another insight: 

Hi  @gbarr12345 ,

I can't open the link you posted containing the example file, and when you say that the measure didn't work, do you mean that the result of the measure didn't contain the value Export Sales Data[Description] = “Premium Red Blend”?

Because I noticed that in your formula there is a space at the end of “Premium Red Blend ”, but there is no space in “Pinot Grigio”.

You might consider using the following dax:

Premium Red & Pinot Grigio Initial Exports =
var StartDate=CALCULATE(MIN('Export Sales Data'[Date]),ALL('Export Sales Data'))
var EndDate=EDATE(StartDate,12)
return
CALCULATE(
    SUM( 'Export Sales Data'[Avg 9LE]),
   FILTER('Export Sales Data',
    'Export Sales Data'[Description] IN {"Premium Red Blend", "Pinot Grigio" }&&
   'Export Sales Data'[Date]>= StartDate&&
'Export Sales Data'[Date]< EndDate
))

“What's the best way to put this into a graph in Power BI?” are you referring to which visual to use to display, you can follow your needs:

If you're just showing the value of the measure alone, you can use the Card visual.

If you want to show the data of the whole table succinctly, you can use table visual.

If you want to display the data in groups, you can use the Clustered column chart.

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

[gbarr12345]

Hi Liu,

That's great, thank you for your assistance.

I'm all sorted now thank you.

Kind Regards,

Greg

[amitchandak]

@gbarr12345 , I all such cases use date table joined with the date of your table. Date table should be used in slicer, measure and visual 

M1=

CALCULATE(

    SUM( Export Sales Data[Avg 9LE]),

    Export Sales Data[Description] IN {"Premium Red Blend ", "Pinot Grigio" } )

M1 last 12 months = Rolling 12 = CALCULATE([M1]),DATESINPERIOD('Date'[Date],max('Date'[Date]),-12,MONTH)) 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
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 :radacad sqlbi My Video Series Appreciate your Kudos.

[gbarr12345]

Hi Amit, 

Thanks for that. What's the best way to put this into a graph in Power BI?

Also do the measures above do what I'm looking for in my above query?