Incorrect Figures

DAX DAX Calculations
1

Hi Guys,

I am back again.
Got some issues that i really need help with.

  1. I have a day range that i created in my Tabular Cube.

     Premium In Days: = CALCULATE([Total Premium],
 FILTER('Date',
     'Date'[Date]> TODAY() - [Days Range] && 'Date'[Date] <= TODAY()))

How ever, i want to replace the Today() in the filter context to the last day of the previous month. i tried to use EOmonth but is giving me a wrong year ( 12-04-2015). I need it to look like the below

Premium In Days: = CALCULATE([Total Premium],
FILTER('Date',
    'Date'[Date]> 31-05-2020 - [Days Range] && 'Date'[Date] <= 31-05-2020)) dynamically as that is the last day when the data were process
  1. I have created a banding simply to what i have seen in the past

= DATATABLE(“Banding”, STRING,
“Min”,INTEGER, “Max”,INTEGER, {
{“0 - 250K”,0,250000},
{“250k - 500K”,250001,500000},
{“500k - 750K”,500001,750000},
{“750k - 1M”,750001,1000000},
{“Above 1M”,1000001,9000000000}

image

however, when i calculate the banding by state it works as seen below

Tiv Banding Group: = CALCULATE( [TIV],
           FILTER(VALUES('Property'[State]),
                COUNTROWS(
                     FILTER('Tiv Banding',
                            [TIV] >= 'Tiv Banding'[Min] &&
                               [TIV]< 'Tiv Banding'[Max]))>0))

but when i want to calculate the TIV or Premium by each banding i am getting wrong figures

image

below is what i am doing for the above

Premium Banding Group: = CALCULATE( [Total Premium],
           FILTER(VALUES('Tiv Banding'),
           COUNTROWS(
           FILTER('Tiv Banding',
           [Total Premium] >= 'Tiv Banding'[Min] &&
         [Total Premium]< 'Tiv Banding'[Max]))>0))

Total premium is calculated this way

Sum ( Actual Premium )

All i want to achieve is for each banding group, calculate the premium

Any helps will be much appreciated

2

Hello @alora101,

Thank your for posting your query into the Forum.

By looking at your formula for Tiv Banding Group everything seems to be correct. But when I look at the formula for Premium Banding Group you have referenced the wrong table in it -

Premium Banding Group =
CALCULATE( [Total Premium],
    FILTER(VALUES(**‘Tiv Banding’**),
         COUNTROWS(
                FILTER(‘Tiv Banding’,
                     [Total Premium] >= ‘Tiv Banding’[Min] &&
                            [Total Premium]< ‘Tiv Banding’[Max]))>0))

Since you’ve referenced the disconnected secondary logic table which you’ve created this is why you might be getting the same totals against all the categories. Please try referencing the correct virtual table and you shall get the desired results.

Thanks & Warm Regards,
Harsh

3

A quick look at the last code tells that you are missing context transition, try adding CALCULATE like below and see if it makes any changes.

Premium Banding Group =
CALCULATE (
    [Total Premium],
    FILTER (
        VALUES ( 'Tiv Banding' ),
        CALCULATE (
            COUNTROWS ( 'Tiv Banding' ),
            FILTER (
                'Tiv Banding',
                [Total Premium] >= 'Tiv Banding'[Min]
                    && [Total Premium] < 'Tiv Banding'[Max]
            )
       ) > 0
    )
)
4

Hi

Thanks for your feedback, however the result is repeated

image

5

Hi Harsh,

The reason i took the state out of the filter is because i have a bar chart with each state banding. I have now included it to the calculation but the figure is that of the states and not the over all total premium for each band

image

6

Hello @alora101,

As far as I know the band is created to analyze performance or breakdown of numbers into different segments such as “Customer Segments” , “Salespople Performance”, “State wise Segmentation of Profits” etc and not the band itself.

I’ve never witnessed a video created by Sam or any author where they’ve analyzed the band itself. I’m also providing a link of detailed video on Banding & Segmenting which was created by Sam under the course “Advanced DAX Combinations” for the reference and also there are other several videos on the Education Portal where same concept and methodology has been applied.

Please correct me if I’ve missed any point here.

Thanks & Warm Regards,
Harsh

7

@Harsh

Thanks very much. That was what i used.

Any chance you can assist with the dates and how to link it in the calculation

Premium In Days:= CALCULATE([Total Premium],
FILTER(‘Date’,
‘Date’[Date]> TODAY() - [Days Range] && ‘Date’[Date] <= TODAY()))

what to replace the TODAY() Function with the last day of the previous month

8

Hello @alora101,

You can try out the below formula -

  Premium In Days = 
  CALCULATE([Total Premium],
     FILTER(‘Date’,
       ‘Date’[Date] > LASTDATE( PREVIOUSMONTH( ‘Date’[Date]) ) - [Days Range] && 
       ‘Date’[Date] <= LASTDATE( PREVIOUSMONTH( ‘Date’[Date]) ) ) )

Hoping you find this useful and helps you. :slightly_smiling_face:

Thanks & Warm Regards,
Harsh

9

Hello @alora101,

I’m also attaching the file of my working in case the above mentioned formula doesn’t work out for you.

Date and Premium on Last Day of Previous Month

As you can in the image above it only calculates the figures for the last day of the previous month.

Thanks & Warm Regards,
Harsh

Date and Premium on Last Day of Previous Month.pbix (436.2 KB)

10

Please be sure to check out our DAX Clean Up tool it’s a great way to ensure to make your DAX code easy to read. Thanks!

https://analysthub.enterprisedna.co/dax-clean-up

11

Hi Harsh,

Thanks for your help. I am grateful.
I did tried it but got zero figures as seen below

image
image697×411 16 KB

What i did was the below and it works for now and i dont know if it is the right way to go about it

Premium In Days:=
CALCULATE([Total Premium],
FILTER(‘Date’,
‘Date’[Date]> TODAY()-75- [Days Range] && ‘Date’[Date] <= TODAY()-75
))

12

Hello @alora101,

I think it’s perfectly alright since you’ve just hard coded a number in your formula. Whenever the requirement changes you can definitely alter your number and achieve the desired result. There’s absolutely no harm in hard coding the numbers in a formula unless the requirement continuously keeps on changing on a day-to-day basis.

Thanks & Warm Regards,
Harsh

13

@alora101 a date can’t be greater than LASTDATE and Less than LASTDATE at the same time

14

Hi,

So what do i do then?

at the moment the last process month is may, so i just use that number.

15

Thanks Harsh

I will continue to monitor it. Thanks a lot and i will be coming back shortly for solutions

16

Hello @alora101,

You’re Welcome. :slightly_smiling_face:

I’m glad I was able to help you.

Thanks & Warm Regards,
Harsh