Need Help needed with DAX Code-great for people who work in sales or academic reporting

[Anonymous]

HI,

I working on a report for GMs which spits out total revenue from enrolments for each month. 

We are pretty new to power bi and still learning.

So this is how it goes,

we let our clients use our platform and charge them for enrolments.

So at the end of each month we sent them a invoice for total number of enrolments for that month.

Now the tricky part is if a student unenrolls with a 30 day period we are suppose to refund the money back just like sales return.

When a unenrollment happens on the same month the value of that enrolment is considered as zero and invoice for active total enrolments is generated.

But when a student unenrolls on the next month, a credit is given back to the clients for that enrolments. That at the end of that month they need to pay only cost of new active enrolments on that month subtract by credit for unenrolments happened on that month.

 So when i select month march, it should show me the total cost from active enrolments - unenrolments from previous months if it happened within 30 days of enrolment date.

If anyone could help me with a DAX code to find this would be great.

Click here

Attaching a sample of data for use

Click here

[v-juanli-msft]

Hi @Anonymous 

I'm not clear about your requirement, could you kindly make me more clear?

In your data example

header1	header2	header3	header4	header5	header6
User Name	Course Name	Enrolled		Price
1502921552	Prepare stocks	2/6/2019 23:12	3/13/2019 11:02	$17.00	17

What do "header 4","header6" mean?

Is the "header3" the users' actual enrolment date?

Could you show an exmaple output using excel as you said?

Best Regards

Maggie

[Anonymous]

Hi i have updated the excel. Removed unwanted coloums and gave proper heading.  Plz Click here for updated excel

 Sorry i couldnt explain it better before.

I am not sure how to do calculation in excel, may be thats what i need help in. We all know power bi is nothing but a glorious excel.

In plain words what i need,

when i select month feb in a slicer(Slicer is based on enolrment date now) it should show total invoice for that month. If a student enrols and unenrolls in the same month, that enrolment is valued as zero. i  need a total sum of total active enrolments  happened in that month subtract  credit for that month(Look for credit definition at bottom).

Credit- When a student unenrolls within a 30 days from enrolment date, we need to refund back the amount paid. If it happens in the same month, value of that enrolment becomes zero, whereas if the unenrollment date falls in  next month the cost of that enrolment is refunded as credit which is added in that months invoice.

Hope it explains little bit. Happy to help if you need more assistance.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Wow great job. I am blown. Thats what i need except instead of rows can we show Sum of Amount.

If you could help me bit with this as well, Thats the solution for the tricky part i was looking for from last week.

Thank you so much for doing this much.

[Ashish_Mathur]

You are welcome.  That will involved a fair bit of my effort.  You should have clarified that upfront.  If i get time later, i will look into it.

[Anonymous]

Sure take your time. But if you could do it. It would be so much help.

[Ashish_Mathur · Accepted solution]

Hi,

You may download the PBI file from here.

Hope this helps.

[Anonymous]

no i guess its throwing up some error startingn with relationship.Its many to many(not able to select one to many). And some measure formula returning wrong values.

Do you like me to share a snapshot of the file?

[Ashish_Mathur]

I see no error in my file atleast.

[Anonymous]

The excel file i posted was a small snapshot of the data. Orginal data set has empty cells in unenrollment. You think that could be a reason?? Below is a snapshot of my report, see count of blanks. The total is only little more. I am not sure why blanks are appearing. If you could help, it would be great.

[v-juanli-msft]

Hi @Anonymous 

Thanks for Ashish_Mathur's help!

if this problem still doesn't slove, please let me know or show your expected result as Ashish_Mathur asked.

If this is sloved, could you kindly accept useful reply as a solution so others may be benefit from it.

Best Regards

Maggie

[Ashish_Mathur]

Hi,

I can help if along with sharing the dataset, you can also show me your exact expected result.